LINQ to SQL: Reusable expression for property?

Question

Pardon me for being unable to phrase the title more exact.

Basically, I have three LINQ objects linked to tables. One is Product, the other is Company and the last is a mapping table Mapping to store what Company sells which products and by which ID this Company refers to this Product.

I am now retrieving a list of products as follows:

var options = new DataLoadOptions();
options.LoadWith<Product>(p => p.Mappings);
context.LoadOptions = options;

var products = (
    from p in context.Products
    select new {
        ProductID = p.ProductID,
        //BackendProductID = p.BackendProductID,
        BackendProductID = (p.Mappings.Count == 0)
            ? "None"
            : (p.Mappings.Count > 1)
                ? "Multiple"
                : p.Mappings.First().BackendProductID,
        Description = p.Description
    }
).ToList();

This does a single query retrieving the information I want. But I want to be able to move the logic behind the BackendProductID into the LINQ object so I can use the commented line instead of the annoyingly nested ternary operator statements for neatness and re-usability.

So I added the following property to the Product object:

public string BackendProductID
{
    get
    {
        if (Mappings.Count == 0) return "None";
        if (Mappings.Count > 1) return "Multiple";
        return Mappings.First().BackendProductID;
    }
}

The list is still the same, but it now does a query for every single Product to get it's BackendProductID. The code is neater and re-usable, but the performance now is terrible.

What I need is some kind of Expression or Delegate but I couldn't get my head around writing one. It always ended up querying for every single product, still.

Any help would be appreciated!

Answer 1

I can't give you a better solution than yours, but as a workaround...

Maybe you could create a view in the database, you can drag the view to the designer as you usually do with a table. The you can read the data from the view. I think it will improve the performance.

Good Luck.