LINQ: Related objects

[Dylan Parry]

Hi,

I have two classes (Review and Author) that are representative of tables
in my database. Both classes are marked up LINQ mappings. The database
(and classes) is structured in such a way that each Review has the ID of
the Author that wrote it, eg

[Table(Name = "Authors")]
public class Author
{
...
[Column(Name = "id", IsPrimaryKey = true, IsDbGenerated = true)]
public int ID
{
...
}
...
}

[Table(Name = "Reviews")]
public class Review
{
...
[Column(Name = "author")]
public int AuthorID
{
...
}
...
}

In the database, the "author" column in the "Reviews" table is a foreign
key mapped to the primary key (ID) of the "Authors" table.

What I am doing at the moment is getting an array of Review objects
using LINQ, and then looping through the array using a foreach
statement, then doing another query to get the Author object for each
Review, eg.

Review[] reviews = _db.GetReviews();
foreach (Review review in reviews)
{
Author author = _db.GetAuthorByID(review.AuthorID);
...
}

Now this to me seems awfully inefficient as it's going to doing loads of
queries to the database! Which brings me to my question - how can I do
this sort of thing in a more efficient way?

Can it be done keeping the Review and Author objects separate, or am I
going to have to create some sort of class that has all the information
about a review, ie. the review and author information? If so, how do I
go about writing a LINQ query to do this?

Thanks,

--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk

The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.

 

[Marc Gravell]

You can express relations in LINQ via AssociationAttribute, and then
just navigate in the query; but setting these up correctly isn't
trivial; I'd suggest using the designer and seeing what it generates...

You need to watch for lazy loading creating lots of round-trips, but if
you know that you need both objects you can sometimes flatten them in
the projection (see below). Another, even more efficient option is to
just select (in the final "new") the specific columns from each that you
want (example from Northwind):

// (would be more efficient to just select new {order.Freight,
// order.Customer.CompanyName}
var qry = from order in ctx.Orders
select new { Customer = order.Customer,
Order = order};
foreach (var item in qry.Take(10))
{
Console.WriteLine("{0} {1}", item.Customer.CompanyName,
item.Order.Freight);
}

Marc

 

[Dylan Parry]

You can express relations in LINQ via AssociationAttribute, and then
just navigate in the query; but setting these up correctly isn't
trivial; I'd suggest using the designer and seeing what it generates...

Thanks. I'll take a look into that.

--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk

The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.