CODEWITHSUNDEEP
c#linqentity-framework
Si Thu
Si Thu

Reputation: 1205

EF include with where clause

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

Upvotes: 5

Views: 9622

Answers (3)

Aron
Aron

Reputation: 15772

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.

Upvotes: 2

kd_
kd_

Reputation: 66

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

Upvotes: 2

agentpx
agentpx

Reputation: 1081

Assuming MemberId is unique as per your query example. Try this

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

Upvotes: 0

Related Questions