Is possible to combine 2 IDbContextFactory in one query in C#?

Question

I know how to retrieve this data from DB but not sure how to handle this in C#. I have two methods, first returning Clubs I follow and the second returns the results of the club. I want to create a third one which will return results of all clubs I follow.

ImmutableArray<ClubInActiveSessionForMemberSqlModel> myClubs;
using (var context = _contextFactory.CreateDbContext())
{
    myClubs = await context
        .ClubMembers
        .SetTracking(false)
        .Where(ClubMember =>
            ClubMember.MemberId == memberId &&
            ClubMember.ClubMemberRegistrationStatusTypeId == ClubMemberRegistrationStatusTypes.Registered.Id &&
            (ClubMember.Club!.Session!.StartDateUtc > DateTime.UtcNow || ClubMember.Club!.Session.EndDateUtc > DateTime.UtcNow))
        .Select(ClubMember => new ClubInActiveSessionForMemberSqlModel(
            ClubMember.ClubId,
            ClubMember.Club!.Name,
            ClubMember.Club.Session!.Id,
            ClubMember.Club.Session.Name,
            ClubMember.Club.Session.SessionTypeId,
            ClubMember.Club.ClubStanding!.ClubMatchWins,
            ClubMember.Club.ClubStanding.ClubMatchLosses
        ))
        .ToImmutableArrayAsync();
}

And here is how I get the data about matches:

using (var context = _contextFactory.CreateDbContext())
{
    return await context
        .Clubs
        .SetTracking(false)
        .Where(t => t.Id == ClubId)
        .Select(t => new AllMatchResultsForClubByIdSqlModel(
            ClubId,
            t.Name,
            t.SessionId,
            t.Position1ClubMatches!
                .Where(tm => !completedOnly || (tm.ClubMatchResult != null && tm.ClubMatchResult.WinnerClubId.HasValue))
                .AsQueryable()
                .Select(selectClubMatchResultSqlModelExpression)
                .AsEnumerable(),
            t.Position2ClubMatches!
                .Where(tm => !completedOnly || (tm.ClubMatchResult != null && tm.ClubMatchResult.WinnerClubId.HasValue))
                .AsQueryable()
                .Select(selectClubMatchResultSqlModelExpression)
                .AsEnumerable()))
        .FirstOrDefaultAsync();
}

Is possible to combine these 2 in 1 or how to do that? I saw some similar issues, but more or less that is for SQL syntax like: Multiple Context joining c#

score 1 · Accepted Answer · answered Jan 28 '23 at 12:13

Yes, it is possible to join two queries. You can use 'Join' method.

I'm not going to provide you a full code but you can examine this snippet to get an idea. Use select method to retrieve the data you want after join.

using (var context = _contextFactory.CreateDbContext())
{
    var results = await context
        .Where(...)
        .Join(context.Clubs, 
          ClubMember => ClubMember.ClubId, 
          Club => Club.Id, 
          (ClubMember, Club) => new { ClubMember, Club })

Learn more at Method-Based Query Syntax Examples: Join Operators

Great. Thanks. It was very helpful. Didn't notice this JOIN part. — NewCSharp, Jan 30 '23 at 16:35

Is possible to combine 2 IDbContextFactory in one query in C#?

1 Answers1