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 ?

Answer 1

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).