0

I'm trying to get a list with the items that contain "a" in the name. I'm using entity framework. I have 5 records with an "a" but 5 records without an "a". In the return are all the items. That means that the include statement works but the where statement doesn't work. I have tried several options but it still doesn't work.

Option 1

I think that this piece of code is the most "correct" one (even without working). 
  return _context.StockLists
                .Where(x => x.CompanyId == id)
                .Include(e => e.Stocks)
                .Where(c => c.Stocks.Any(x => x.Name.Contains("a"))).ToList();

Her is no error code. When I run it, the program loaded 10 stocks. This is incorrect because I said in the where that I only want the stocks with an "a" in their name. He loads 10 stocks while this must be 5. Output number of stocks

Option 2

Another solution that I have tried is: 
 return (IEnumerable<StockList>)_context.StockLists.Where(c => c.Stocks.Any(i => i.Name.Contains("a")))
            .Select(c => new
            {
                c,
                Stocks = c.Stocks.Where(i => i.Name.Contains("a"))
            })
            .ToList();

The error code here is:

> System.InvalidCastException: 'Unable to cast object of type 'System.Collections.Generic.List`1[<>f__AnonymousType0`2[AandelenApplicatie.Data.Poco.StockList,System.Collections.Generic.IEnumerable`1[AandelenApplicatie.Data.Poco.Stock]]]' to type 'System.Collections.Generic.IEnumerable`1[AandelenApplicatie.Data.Poco.StockList]'.'

Option 3

A third option that I've tried is: 
      return _context.StockLists
                .Include(b => b.Stocks.Where(p => p.Name.Contains("a")))
                .ToList();

Here crashes my application with the error:

System.InvalidOperationException: 'Lambda expression used inside Include is not valid.'

I suppose that something is wrong with the statement but I can't find an answer to it. Thanks for your help.

JaDa-ma
  • 47
  • 8
  • 2
    Can you elaborate on how your code "doesn't work"? What were you expecting, and what actually happened? If you got an exception/error, post the line it occurred on and the exception/error details which can be done with a [mre]. Please [edit] your question to add these details into it or we may not be able to help. – gunr2171 Apr 15 '21 at 15:55
  • I think that Option 1 filters the stock lists by stock, not the stocks themselves. See: https://stackoverflow.com/a/39637702/880990 – Olivier Jacot-Descombes Apr 15 '21 at 16:32
  • This return (IEnumerable)_context.StockLists .Select(x => new { Stocks = x.Stocks.Where(h => h.IsDeleted) }) Gives error:System.InvalidCastException: 'Unable to cast object of type 'System.Collections.Generic.List`1[<>f__AnonymousType0`1[System.Collections.Generic.IEnumerable`1[AandelenApplicatie.Data.Poco.Stock]]]' to type 'System.Collections.Generic.IEnumerable`1[AandelenApplicatie.Data.Poco.StockList]'.' – JaDa-ma Apr 15 '21 at 17:27
  • Try `new StockList{ Stocks:= x.Stocks.Where(....`. You want to have `StockLists`. If you just say `new { ... }` you are getting an anonymous type. – Olivier Jacot-Descombes Apr 15 '21 at 19:25

0 Answers0