CODEWITHSUNDEEP
c#linqentity-frameworkforeach
Zaki
Zaki

Reputation: 5600

Eliminate loop and use Linq instead

Say I have a List as below:

 List<R> lstR = GetR();

Now I want a Linq statement to get menus assigned to R, I achieved this by using a loop and then using Linq to get the menus as below:

 List<int> ids = new List<int>();
 foreach (R r in lstR)
 {
   ids.Add(r.Id);
 }

 menu = (from s in db.Menu
         where ids.Contains(s.R.Id)
         select s.MenuText).Distinct();

Now as far as I know the above is two loop(Linq is using internal loop). would I be able to combine these two statements i.e. not do the first loop to get the ids?

Upvotes: 2

Views: 1047

Answers (5)

Alex Zhukovskiy
Alex Zhukovskiy

Reputation: 10015

menu = db.Menu.Where(s => GetR().Select(r => r.Id).Contains(s.R.Id))
                     .Select(s => s.MenuText)
                     .Distinct();

but it will be to complex. It will be better if you'l write like this

        var ids = GetR().Select(r => r.Id);

        menu = db.Menu.Where(s => ids.Contains(s.R.Id))
                      .Select(s => s.MenuText)
                      .Distinct();

Upvotes: 1

p.s.w.g
p.s.w.g

Reputation: 149020

In both lstR and db.Menu are either in-memory data sets (Linq-to-Objects) or IQueryable collections from your database, you can do this:

menu = 
    (from s in db.Menu
     where lstR.Select(r => r.Id)
               .Contains(s.R.Id)
     select s.MenuText)
    .Distinct();

Or this:

menu = 
    (from s in db.Menu
     join r in lstR on s.R.Id equals r.Id
     select s.MenuText)
    .Distinct();

However, since List<R> exists in memory and db.Menu is an IQueryable, you're options are limited. You could materialize db.Menu into an IEnumerable, so you can process it in memory:

List<R> lstR = GetR();
menu = 
    (from s in db.Menu.AsEnumerable()
     join r in lstR on s.R.Id equals r.Id
     select s.MenuText)
    .Distinct();

But, this can be costly if there are a lot of records. It's better to do something like this, which admittedly doesn't look much different from what you already have:

List<R> lstR = GetR();
var ids = lstR.Select(r => r.Id).ToList(); // or .ToArray();
menu = 
    (from s in db.Menu
     where ids.Contains(s.R.Id)
     select s.MenuText)
    .Distinct();

But in truth, the best option is to see if you can refactor GetR so that it returns an IQueryable<R> from your database. That way you can use both of the first two options without needing to materialize any sets into memory first. And by the way, once you've done that and set up navigation properties, you can probably do something like this: 

IQueryable<R> lstR = GetR();
menu = 
    (from r in lstR
     from s in r.Menus
     select s.MenuText)
    .Distinct();

Upvotes: 3

user2711965
user2711965

Reputation: 1825

Use Join

var result = (from s in db.Menu
              join r in lstR on s.Id equals r.ID
              select s.MenuText).Distinct();

Upvotes: 0

Jeroen van Langen
Jeroen van Langen

Reputation: 22038

It can be done like.

menu = (from s in db.Menu
        where lstR.Select(item => item.Id).Contains(s.R.Id)
        select s.MenuText).Distinct();

But i wouldnt combine those two statements, because if you use a HashSet it will speed up:

var ids = new HashSet<int>(lstR);

menu = (from s in db.Menu
     where ids.Contains(s.R.Id)
     select s.MenuText).Distinct();

This will be faster i guess. The problem with the first one is, every s in db.Menu The list is iterated for creating a list of id's select().

Upvotes: 2

Rob van der Veer
Rob van der Veer

Reputation: 1148

You coud use the linq projection method Select():

ids = lstR.Select(p => p.Id);

Upvotes: 1

Related Questions