CODEWITHSUNDEEP
entity-frameworkrepository-patterndbcontext
gaurus
gaurus

Reputation: 426

Filtering related entities in entity framework 6

I want to fetch the candidate and the work exp where it is not deleted. I am using repository pattern in my c# app mvc.

Kind of having trouble filtering the record and its related child entities

I have list of candidates which have collection of workexp kind of throws error saying cannot build expression from the body.

I tried putting out anonymous object but error still persist, but if I use a VM or DTO for returning the data the query works.

It's like EF doesn't like newing up of the existing entity within its current context.

var candidate = dbcontext.candidate
    .where(c=>c.candiate.ID == id).include(c=>c.WorkExperience)
    .select(e=>new candidate
    {
        WorkExperience = e.WorkExperience.where(k=>k.isdeleted==false).tolist() 
    });

Is there any workaround for this?

Upvotes: 0

Views: 875

Answers (2)

Dario Griffo
Dario Griffo

Reputation: 4274

var candidate = 
    from(dbcontext.candidate.Include(c=>c.WorkExperience)
         where(c=>c.candiate.ID == id)
         select c).ToList().Select(cand => new candidate{WorkExperience = cand.WorkExperience.where(k=>k.isdeleted==false).tolist()});

Upvotes: 0

takemyoxygen
takemyoxygen

Reputation: 4394

You cannot call ToList in the expression that is traslated to SQL. Alternatively, you can start you query from selecting from WorkExperience table. I'm not aware of the structure of your database, but something like this might work:

var candidate = dbcontext.WorkExperience
    .Include(exp => exp.Candidate)
    .Where(exp => exp.isdeleted == false && exp.Candidate.ID == id)
    .GroupBy(exp => exp.Candidate)
    .ToArray() //query actually gets executed and return grouped data from the DB
    .Select(groped => new {
        Candidate = grouped.Key,
        Experience = grouped.ToArray()
    });

Upvotes: 1

Related Questions