Combine 2 results into one in Entity Framework Core

Question

I have 2 queries as follows:

var q1 = await context.Submissions                
                      .Include(s => s.Application)               
                      .ToListAsync();

// q1 is of type List<Submissions>

var q2 = await context.Applications
                      .Select(a => new Application
                                       { 
                                           Id = a.Id, 
                                           Member = a.Histories.OrderByDescending(h => h.ModifiedDate).FirstOrDefault().Member
                                       }).ToListAsync();

// q2 is of type List<Applications>

Is there a way to combine these 2 queries and have the type as List<Submissions>?

Note: I'm using EF Core version 3

Submissions class:

    public class Submission
    {
        public Guid Id { get; set; }
        public string Name { get; set; }        
        public Application Application { get; set; }
        public Guid? ApplicationId { get; set; }
    }

Applications class:

 public class Application
    {
        public Guid Id { get; set; }
        public string Member { get; set; }   
        public ICollection<History> Histories { get { return _Histories; } set { _Histories = value; _currentMember =null; } }        
        private ICollection<History> _memberHistories;       
        private MemberHistory _currentMember = null;        
        
    }

Could you please share the details of `Submissions` and `Applications` class? — Md Farid Uddin Kiron, Jul 15 '22 at 08:00
Don't waste your time in case you have no good reason. The LINQ operator is `Queryable.Concat` (after removing `ToList` and similar), but it is *not* supported for such type of queries (which contain subcollections) in EFC v.3.x, 5.x and 6 .x (not sure what it would be in 7.0). In case you don't need additional filtering, grouping, ordering etc. on the resulting db query, do it client side (as you already did). And even if you need such features, currently no way to achieve the "single db query" goal :-) — Ivan Stoev, Jul 15 '22 at 08:07

score 0 · Answer 1 · answered Jul 15 '22 at 12:36

There are .Include() and .ThenInclude()

var q1 = context.Submission
                    .Include(submission => submission.Application)
                    .ThenInclude(application = > application.Histories);

having too many includes can give performance issues, unless you actually start splitting up the query. Another approach would be to contain it in a select statement which often performs better. but gives sort of a split result.

var q2 = context.Submissions.Select(submission => new 
{
  SubMission = submission
  Application = submission.Application
});

var result = q2.ToList().Select(t => t.Submission);

due to the built in EF Core mapper, the relations are handled for you so application are loaded and "attached" correctly to the Submission set on the result.

Thank you! Can you please answer https://stackoverflow.com/questions/72999307/simplify-querying-sql-in-entity-framework-core — user989988, Jul 15 '22 at 21:20

Combine 2 results into one in Entity Framework Core

1 Answers1