A better way to do this LINQ query?

Question

I've got some collections of data objects that can't directly be accessed from one another. I imagine the best solution would be to get the database guys to make a query for this, but in the meantime, is there some way to tighten this up?

var conflicting = allFoos.Where(foo => foo.ElectronicSerialNumber != 0 
                                && foo.BarID != interestingBar.ID)
                    .Join(fooInfoCollection, foo => foo.ElectronicSerialNumber, 
                                             fooInfo => fooInfo.ElectronicID, 
                                             (foo, fooInfo) => foo)
                    .Join(allBars, foo => foo.BarID, bar => bar.ID, (foo, bar) => bar)
                    .Where(bar => bar.SomeCriteria == false)
                    .FirstOrDefault();
if (conflicting != null)
{
   doStuff(conflicting);
}

Answer 1

It looks like you have fetched all Foos, all FooInfos and all Bars from the database so that you can do a query where you only actually need one Bar object as the result. If you had to get all these objects out of the database anyway for another query then this is OK, but if you got all these objects just for this one query then this is extremely inefficient. You should try to get the database to perform the query and only return the one object you need.

Even if there are no foreign key constraints, you can still make joins, and you can set up relationships between types in Linq To SQL even if they don't actually exist in the database, making it easier to formulate your queries.

Answer 2

It can be difficult to stylisticly accept the joins that Lambda syntax uses. Query comprehension syntax has a much better style for joining. The same operations are performed.

Bar conflicting = 
(
  from foo in allFoos
  where foo.ElectronicSerialNumber != 0
  where foo.BarID != interestingBar.ID
  join fooInfo in fooInfoCollection
  on foo.ElectronicSerialNumber equals fooInfo.ElectronicID
  join bar in allBars
  on foo.BarID equals bar.ID
  where !bar.SomeCriteria
  select bar
).FirstOrDefault();

Note, foo and fooInfo (and bar) are in-scope in the select clause, if you want to use them.