Convert SQL to Entity Framework Linq convert to list C#-CodePudding

I am stuck at selecting a list of ID based on another list of ID in Entity Framework.

I googled and saw some examples but I am unable to find the answer I am looking for

SELECT ID 
FROM Main m 
INNER JOIN MainMapping cm ON mm.MainId = m.ID
WHERE mm.SecondId IN (1, 2, 3, 4, 5, 6)

I am trying to convert this SQL to EF Linq.

Here is what I got so far:

var listOfId = await _context.Main
                      .Include(main => main.MainMapping)
                      .Where(main => main.MainMapping 
                          .Where(mapping => request.SecondIdList.Any(y => y == mapping.SecondId)))
                      .ToListAsync();

But I keep getting errors. What am I missing?

These are the errors:

Error CS1662 Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type

Error CS0029 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<Testing1.Domain.Entities.MainMapping>' to 'bool'

CodePudding user response：

Ok, first, everytime you call .Where to filter something, you must return a boolean expression. Given you are using Linq to SQL your expression should return something that is possible to parse to sql language. You could try using .Any on your collection property to return a boolean and Contains to generate an IN statement. Then, to return a collection of Id, use Select to change the output. EF will parse it to valid sql expression.

var listOfId = await _context.Main
    .Include(main => main.MainMapping)
    .Where(main => main.MainMapping.Any(mapping => request.SecondIdList.Contains(mapping.SecondId)))    
    .Select(man => main.Id)
    .ToListAsync();