CODEWITHSUNDEEP
c#linq
Harry
Harry

Reputation: 3195

Refactor Linq query

I am trying to cut this linq down

 var sys = db.tlkpSystems
 .Where(a => db.tlkpSettings.Where(e => e.Hidden < 3)
 .Select(o => o.System)
 .ToList().Contains(a.System))      //cannot get this part in?
 .OrderBy(a => a.SystemName).ToList();

foreach (var item in sys)
    model.Add(new SettingSystem { 
        System = item.System, 
        SystemName = item.SystemName 
});

I have tried the following:

   List<SettingSystem> model = new List<SettingSystem>();
   model = db.tlkpSettings.Where(e => e.Hidden < 3)
     .OrderBy(e => e.Setting)
     .Select(e => new SettingSystem
     {
      System = e.System,
      SystemName = e.Setting
      }).ToList();

How can I call the .Contains(a.System) part in my query?

Thanks

Upvotes: 2

Views: 86

Answers (2)

David
David

Reputation: 10708

As an addendum, there is also AsEnumerable for when you must pull a query into memory (such as calling methods within another clause). This is generally better than ToList or ToArray since it'll enumerate the query, rather than enumerating, putting together a List/Array, and then enumerating that collection.

Upvotes: 1

Ivan Stoev
Ivan Stoev

Reputation: 205579

Some general rules when working with LINQ to Entities:

  • Avoid using ToList inside the query. It prevents EF to build a correct SQL query.

  • Don't use Contains when working with entities (tables). Use Any or joins.

Here is your query (in case System is not an entity navigation property):

var sys = db.tlkpSystems
    .Where(a => db.tlkpSettings.Any(e => e.Hidden < 3 && e.System == a.System))
    .OrderBy(a => a.SystemName).ToList();

Upvotes: 3

Related Questions