CODEWITHSUNDEEP
c#linq-to-sqlorm
Thomas C. G. de Vilhena
Thomas C. G. de Vilhena

Reputation: 14565

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

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.

Upvotes: 1

Views: 2606

Answers (2)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28708

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;
}

Upvotes: 0

TheEvilPenguin
TheEvilPenguin

Reputation: 5672

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);
        }
    }
}

Upvotes: 3

Related Questions