score:0

Accepted answer

When there are navigation properties, use them! Your Product entity has a navigation property ProductSettings. That means that the query could look like this:

var products = from p in context.Products
               from ps in p.ProductSettings
               where p.CreatorID == 1 && ps.ProdCreatorDisplay == 1
               select new ProductsAll() 
               { 
                    ProdTitle = p.ProdTitle,
                    ProdPrice = ps.FirstOrDefault().ProdPrice
               };

(Note that I stubbornly refuse to use the tbl prefix)

But that does not tackle the repetitive code issue.

Enter AutoMapper. This is a nice little tool that removes boilerplate property copying statement from your code by defining and executing mappings between objects. The default mapping is based on name convention. E.g. the statement Mapper.CreateMap<Product,ProductDto>(); configures a mapping between all equally named properties of Product and ProductDto. And Mapper.Map<ProductDto>(product); would create a ProductDto object from product.

But your ProductsAll class has a property ProdPrice that comes from ProductSettings. How to deal with that?

Here a nice feature of AutoMapper can be used. It also resolves properties of nested objects based on name convention. Suppose your class Product had a property Title, AutoMapper can resolve that to a property ProductTitle in the target object. Let's use that:

class ProductsAll
{
    // ProductSetting should have a Product property.
    public string ProductTitle { get; set; }
    // Assuming ProductSetting has a property ProdPrice.
    public decimal ProdPrice { get; set; }
}

...

Mapper.CreateMap<ProductSetting,ProductsAll>(); // ProductSetting!

var q = from ps in context.ProductSettings
        where ps.Product.CreatorID == 1 && ps.ProdCreatorDisplay == 1;

// Now the magic!
var products = q.Project().To<ProductsAll>();

Project() is an extension method on IQueryable. The nice thing is that the SQL statement only contains the properties used in the projection. As you see, you can define a mapping once and for the rest use the Project().To() construct in your code.

There's much more to AutoMapper. This is a quick start.

score:0

Since you've tagged this , I'm guessing you are using Entity Framework. In that case, you can tell linq it to load the settings for your products without explicitly specifying the join. I'm assuming here your products has a relationship to your product settings table which is exposed via a Settings property on your product entity.

var allProducts = context.tblProducts
    .Where(p => p.CreatorID == 1 && p.Settings.ProdCreatorDisplay == 1)
    .Include(p = p.Settings)
    .Select(p => CreateViewModelFromFullyPopulatedProductModel(p))
    .ToList();

Once you have the full settings loaded, you can just write a mapping function that does the mapping for you. Of course this will load all of the properties of the products and the settings for each of those products. That can be a major concern depending on how many products you are loading and how many unused properties they have, but from your question, it sounds like you are loading most of these values for the view model already.


Related Articles