EF include with where clause

Question

I've Resource and ResourceDetail. MemberPoint with memberId and ResourceId.

I would like to get Resources Details for a member.

In SQL,

Select d.* From ResourceDetails d Inner Join 
        Resource on r d.ResourceId = r.Id Inner Join 
        MemberPoint mp on r.id = mp.ResourceId 
        where mp.memberId = 1

In EF,

   var query = _context.ResourceDetails
            .Include(d => d.Resource)
            .Include(r => r.Resource.Memberpoints)
            .Where(e => e.Resource.Memberpoints.Where(m => m.MemberId))

I got error when I write above EF query.

Error: unknown method 'Where(?)'of System.Linq.IQueryable

Answer 1

You can try using include this way:

var query = _context.MemberPoint.Include("Resource.ResourceDetails")
                                .Where(m => m.MemberId == 111111);

Or try joining on resourceId and selecting an anonymous type with the data you need:

var query = (from m in _context.MemberPoint
            join rd in _context.ResourceDetails on m.ResourceId equals rd.ResourceId
            where m.MemberId == 11111
            select new
            {
               Member = m,
               ResourceDetail = rd
            })

Answer 2

You are using EF completely incorrectly.

What you want is actually

If ResourceDetails has one Resource and each reasource has one member (unlikely).

var query = _context.ResourceDetails
        .Include(d => d.Resource)
        .Include(r => r.Resource.Memberpoints)
        .Where(d => d.Resource.Memberpoints.MemberId == 1);

If ResourceDetails has one Resource and each resource can have multiple Members.

var query = _context.ResourceDetails
        .Include(d => d.Resource)
        .Include(r => r.Resource.Memberpoints)
        .Where(d => d.Resource.Memberpoints.Any(m => m.MemberId == 1));

If ResourceDetails has multiple Resources (unlikely) and each resource can have multiple Members.

var query = _context.ResourceDetails
        .Include(d => d.Resource)
        .Include(r => r.Resource.Memberpoints)
        .Where(d => d.Resource.Any(r => r.Memberpoints.Any(m => m.MemberId == 1)));

Okay. So what about the join you wanted? Well that is the job of the ORM. The ORM mapping already knows how ResourceDetails are linked to Members.

So what was that error you got?

Well, the sig of IQueryable.Where() takes a Func<T, bool> and returns an IQueryable<T>.

So in your example, the inner Where is wrong because you are giving it a Func<T, int>. The outter Where is wrong because you are passing a IQueryable<T> to it (although the compiler doesn't know that because its all sorts of wrong already).

TL:DR

In general, don't join with EntityFramework/Linq. EF should have the associations in the mappings and already knows how to join entities together.