Complex queries in Entity Framework Core

Question

I have these two entities in EF Core:

public class Chat
{
    public int Id { get; set; }
    public string SessionId { set; get; } = string.Empty; // Could be sessionId or anything that indicate the chatId from the source
    public string Source { set; get; } = string.Empty; // Any Chat Source

    public ChatAnalysisResult? AnalysisResult { get; set; }
    public ICollection<Message> Messages { set; get; } = new List<Message>();
}

public enum JobType
{
    [Description("ChatFetch")]
    ChatFetch,
    [Description("ChatAnalyze")]
    ChatAnalyze,
    [Description("RecordFetch")]
    RecordFetch,
    [Description("RecordTranscript")]
    RecordTranscript
}

public enum JobStatus
{
    [Description("Pending")]
    Pending,
    [Description("Started")]
    Started,
    [Description("Finished")]
    Finished,
    [Description("Stopped")]
    Stopped,
    [Description("Failed")]
    Failed
}

public class Job
{
    public int Id { get; set; }
    public JobStatus status { get; set; } = JobStatus.Pending;
    public List<string> Ids { get; set; } = new();
    public List<string> Pages { get; set; } = new();
    public JobType Type { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime? StartedAt { get; set; }
    public DateTime? EndedAt { get; set; }
    public List<JobOption> JobOptions { get; set; } = new();
}

This is my original code:

var queryable = context.Chats
    .Include(chat => chat.Messages)
    .Include(chat => chat.AnalysisResult)
    .AsQueryable();

if (query.AnalyzedOnly is not null && (bool)query.AnalyzedOnly)
{
    queryable = queryable.Where(c => c.AnalysisResult != null);
}

var response = await queryable
    .ApplySorting(query)
    .ApplyPagination(query)
    .ProjectTo<ChatResponse>(mapper.ConfigurationProvider)
    .ToArrayAsync(ct);
        
await SendOkAsync(response, ct);

Now what I want to do is to check if there is a Job that contains a chat id in the Job.Ids list, the job status should be Pending or Started, and the job type should be ChatAnalyze.

I tried doing this:

var queryable = context.Chats
    .Include(chat => chat.Messages)
    .Include(chat => chat.AnalysisResult)
    .AsQueryable();

if (query.AnalyzedOnly is not null && (bool)query.AnalyzedOnly)
{
    queryable = queryable.Where(c => c.AnalysisResult != null);
}

var result = await queryable
    .ApplySorting(query)
    .ApplyPagination(query)
    .Select(chat => new
            {
                Chat = chat,
                Analyzing = context.Jobs
                    .Where(j => (j.status == JobStatus.Pending || j.status == JobStatus.Started) &&
                                j.Type == JobType.ChatAnalyze &&
                                j.Ids.Contains(chat.Id.ToString()))
                    .Any()
    })
    .ToArrayAsync(ct);

var response = new List<ChatResponse>();

foreach (var element in result)
{
    var res = mapper.Map<ChatResponse>(element.Chat);
    res.Analyzing = element.Analyzing;
    response.Add(res);
}

await SendOkAsync(response, ct);

The produced query seems right:

SELECT 
    "t"."Id", ... , 
    EXISTS (SELECT 1
            FROM "Jobs" AS "j"
            WHERE "j"."status" IN (0, 1) 
                AND "j"."Type" = 1 
                AND CAST("t"."Id" AS TEXT) IN (SELECT "i"."value"
                                                FROM json_each("j"."Ids") AS "i"))
FROM 
    (SELECT 
            "c"."Id", "c"."SessionId", "c"."Source"
        FROM 
            "Chats" AS "c"
        ORDER BY 
            "c"."Id" DESC
        LIMIT 
            @__p_1 OFFSET @__p_0) AS "t"
LEFT JOIN 
    "ChatAnalysisResults" AS "c0" ON "t"."Id" = "c0"."ChatId"
LEFT JOIN 
    "Messages" AS "m" ON "t"."Id" = "m"."ChatId"
ORDER BY 
    "t"."Id" DESC, "c0"."Id"

But I don't really know if this is the right way to do this, any opinions on how to make this better ?

How can query.AnalyzedOnly be a boolean? Where is AnalyzedOnly in the class model? — Gert Arnold
– Gert Arnold, Commented Jun 20, 2024 at 15:41
Its just a query parameter in the request query dto so I can filter based on it @GertArnold — Mohammad Zohar
– Mohammad Zohar, Commented Jun 25, 2024 at 6:04

Tjark Prokoph · Accepted Answer · 2024-06-20 11:59:44Z

2

I would suggest to change the subquery on the Job entity to directly use .Any() with a predicate instead of .Where().Any(). This improves readability and has a minor performance improvement, since .Where() first needs to set up an iterator.

context.Jobs
       .Any(j => (j.status == JobStatus.Pending || 
                  j.status == JobStatus.Started) &&
                 j.Type == JobType.ChatAnalyze &&
                 j.Ids.Contains(chat.Id.ToString()))

For building the response you can also use LINQ. I am assuming you are using AutoMapper. Instead of using .ToArrayAsync() you can transform it into an IEnumerable with .AsEnumerable(). You should be able to perform the .Select() with the mapping logic on the IEnumerable. Afterwards you can use .ToArrayAsync() or .ToList().

You might also want to structure your code, since at least building your query should be separated from mapping the entities and returning the result to the client (e.g. defining a QueryBuilder and/or a Repository).

answered Jun 20, 2024 at 11:59

Tjark Prokoph

864 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mohammad Zohar Over a year ago

Sorry for being late to respond, thanks this is very useful I will be implementing that for sure.

Collectives™ on Stack Overflow

Complex queries in Entity Framework Core

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related