Linq to SQL: Populating an extended generated class in a Linq Query

Question

Ok so, I have a class that's generated when I created the Linq to SQL connection.

It basically looks like this:

    public partial class Product
    {
        public string Name { get; set; }
        public string Description { get; set; }
    }

I extended it by adding another property, like this:

    public partial class Product
    {
        public string Category { get; set; }    
    }

Now I have a Linq query to get the product details, like this:

    public List GetProducts()
    {
        using(MyDataContext dc = new MyDataContext())
        {
            var products = from p in dc.Products
                           select p;
        }
    }

The new field I added(Category) can be retrieved in the Linq query by using "p.Category.Description".

So my question is, which is that best way to populate the Product class including the new Category property.

I'd prefer if there was a way to automatically populate the generated portion of the Product class by using the data in the Linq query "select p".

I've also thought of creating a constructor on my partial class that accepts the product (select P) and the Category(select p.Category.Description)

Doing something like this:

    // Linq query calling the constructor
    var products = from p in dc.Products
                   select new Product(p, p.Category.Description)

    // Product Constructor in my extended partial class
    public Product(Product p, string CategoryDescription)
    {
        this = p; // there must be a way to do something like this
        Category = CategoryDescription;
    }

But is there a way to do this without having to list every property in the Product class and set each of them individually ??

Answer 1

If you want to re-use your existing code you would have to create Poco's instead:

var products = from p in dc.Products
               select new Product
               {
                   Name = p.Name,
                   Description = p.Description,
                   Category = p.Category.Description
               }

Or to make it even easier, just do:

public partial class Product
{
    public string Category
    {
        get { return this.Category.Description; }
        set { this.Category.Description = value; }
    }
}

Answer 2

LINQ-to-SQL plays nicely as long as you let it do its thing by itself. IMO, the best way to handle this scenario is to leave the DBML-generated type at the DAL - only return those if that is exactly a 100% fit for what you want to return.

Instead, I would create a new type that is not managed by LINQ-to-SQL, and return this instead (you can do initializer-based projections for non-DBML types); for example:

var products = from p in dc.Products // p is implicitly a Product
               select new ExtendedProduct  // our completely unrelated type
               {
                   Id = p.Id,
                   Name = p.Name,
                   //...
                   Category = p.Category.Description
               };

This should allow you to get your data out, without any silent n+1 issues, and without the data-context complaining. It also means you are in complete control over which columns are queried and how they are mapped.

Essentially this then uses LINQ-to-SQL just to do the database integration, using our own type for the main application.

Answer 3

It looks like what you're trying to do is something like this:

using(MyDataContext dc = new MyDataContext())
{
    var products = from p in dc.Products
                   select p;
    foreach(Product p in products)
        p.Category = p.Category.Description;
}

... which doesn't make any sense to me. To start with, one of the "Category"s will have to be called something else.