How to map a collection of objects using LINQ to SQL?

Question

I'm doing some tests to get a taste of object-relational mapping using LINQ to SQL and I've got stuck trying to make a many-to-many relationship work. To keep it simple, suppose I have an Order class that owns a collection of ordered Products. How can I map this product collection using LINQ to SQL?

[Table(Name = "OrderTable")]
public class Order
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int ID;

    [Column]
    public string Customer;

    [Association(ThisKey = "ID", OtherKey = "ID")] // Not working
    public EntitySet<Product> products = new EntitySet<Product>();
}

[Table(Name = "ProductTable")]
public class Product
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int ID;

    [Column]
    public string Name;

    [Column]
    public double Price;
}

I tried using an EntitySet as a container to hold an Order's Products but it didn't work because LINQ queries are returning Order objects with empty products collections.

PS: From This question it seems that I need to create a third class to keep track of Order-Product relations, but I was hoping to find a way of doing it without having to create another class.

TheEvilPenguin · Accepted Answer · 2012-05-14 00:48:19Z

3

There isn't a way to model this in the database apart from an Order-Product table, and Linq-to-SQL entities match SQL tables. Unfortunately this is the only way (as far as I know) to model this with Linq-to-SQL.

After you've created the model, you can create a property to hide this limitation by right-clicking on the entity and choosing View Code (assuming DBML designer, otherwise just add it to code), then adding something like this:

public partial class Order
{
    public IEnumerable<Product> Products
    {
        get
        {
            return OrderProducts.Select(op => op.Product);
        }
    }
}

answered May 14, 2012 at 0:48

TheEvilPenguin

5,7022 gold badges30 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kirk Broadhurst · Accepted Answer · 2012-05-14 01:24:29Z

0

As @TheEvilPenguin points out,

LINQ-to-SQL maps classes to tables
You need an OrderProduct table to facilitate this many-many relationship in the database
therefore, LINQ-to-SQL will need to map that table too.

Drag that join table onto the designer. You'll have a property on the Order named OrderProduct, and you'll be able navigate to the Products on an Order through the OrderProducts property

foreach (var op in Order.OrderProducts)
{
    var product = op.Product;
}

answered May 14, 2012 at 1:24

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

Collectives™ on Stack Overflow

How to map a collection of objects using LINQ to SQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related