Linq where clause not working when including another table

Question

I have a linq condition that i include a second table. On the second table i have a where condition to give me records that are not deleted. how ever the where condition is ignored and it returns items that was deleted.

var test= (await table1.GetListAsync(q => q.Where(x => x.Name == command.Name && x.Id == command.Id && x.Deleted==false).Include(x => x.Tables2.Where(y => y.Deleted == false)))).FirstOrDefault();

in the above code table1 where clause executes correctly but the where clause on table 2 returns deleted records.

Table 1 model looks as follows

public class Table1: IdentityModel<string>
{
   public string Name { get; set; }
   public string Id { get; set; }
   public bool Deleted { get; set; }
   public IList<Table2> Tables2{ get; set; } = new List<Table2>();
}

What am i doing wrong?

What version of EF are you using or are you using something else? — Igor, Jul 13 '23 at 12:59

score 0 · Answer 1 · answered Jul 13 '23 at 13:07

LINQ doesnt contain Include extention method. Seems you are speaking about EntiryFramework that serves different purposes - eager loading related fields.

Depending on your requirements, you might want to follow one of the following approaches

Filter data in memory

var test = (await table1.GetListAsync(q => q.Where(x => x.Name == command.Name && x.Id == command.Id && x.Deleted==false)
    .Include(x => x.Tables2))).FirstOrDefault();

// Now filter in memory
if (test != null)
{
    test.Tables2 = test.Tables2.Where(t => !t.Deleted).ToList();
}

Use a projection

var test = await table1
    .Where(x => x.Name == command.Name && x.Id == command.Id && x.Deleted==false)
    .Select(x => new {
        Table1 = x,
        Tables2 = x.Tables2.Where(y => !y.Deleted)
    })
    .FirstOrDefaultAsync();

It's not entirely clear what data you want to fetch, but you can use the code abot as starting point.

Linq where clause not working when including another table

1 Answers1