CODEWITHSUNDEEP
c#linqlinq-to-sqlc#-4.0
mnsr
mnsr

Reputation: 12437

Linq To SQL: Most Efficient Way of Looping through List of ID's

Say I have a list of ID's:

int[] ids = { 1, 2, 3, 4 };

I already have a compiled LINQ statement that returns the individual row (lets call them "Bar" here). So I could just do this:

var foo = new List<Bar>();
var len = ids.Length;
for (int i=0; i < len; i++)
{
    foo.Add(db.GetBarByID(ids[i]));
}

What I'm wondering is, if there's a more efficient way of doing this? There's not that much data being returned (several nvarchar and int columns) per row and the list of ID's can be up to 50.

Update:

I'll elaborate on "GetBarByID". It's a simple LINQ statement which returns "Bar". 

class Bar
{
    public int ID {get; set;}
    public string Name {get;set;}
    public int Age {get;set;}
    public string Blah{get;set;}
}

IQueryable<Bar> GetBarByID(int ID)
{
    return db.Bar
            .Where(w => w.Barid == ID)
            .SelectMany(b => Othertable.Where(w => w.barid == b.id), 
                (b, x) => new Bar { ID = s.id, Name = s.name, Age = s.age, Blah = x.blah });
}

Side note: By efficient, i mean clean code and performance wise.

Upvotes: 1

Views: 1927

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1500065

There's certainly a simpler way of writing the same code:

var foo = ids.Select(id => db.GetBarById(id))
             .ToList();

However, it depends on what db.GetBarById really does. If you can use ids itself in your query, you might be able to do the whole thing in a single database query:

var foo = db.Bars
            .Where(b => ids.Contains(b.Id))
            .ToList();

Obviously that's not using your existing LINQ query though - if there's more involved in retrieving a single row, you may need to do a bit more work.

EDIT: Okay, now we've got the single method, it's fairly easy... although you should probably be using a join, to be honest... and I suspect your real code has w.Barid == ID rather than w.Barid = ID.

var foo = db.Bar
            .Where(w => ids.Contains(w.Barid))
            .SelectMany(b => Othertable.Where(w => w.barid == b.id), 
                                (b, x) => new Bar { ID = s.id, Name = s.name, 
                                                    Age = s.age, Blah = x.blah })
            .ToList();

Upvotes: 4

jgauffin
jgauffin

Reputation: 101140

var myProducts = from bar in db.Bars
             where ids.Contains(bar.Id)
            select bar;

Upvotes: 3

Related Questions