score:0
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 sql, 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.
Source: stackoverflow.com
Related Articles
- Join tables and project into a new model
- How to use Linq or Lambda to join 1-to-many tables and project flattened results into an anonymous type
- How can I use LINQ to project this parent and children object model into a flat, single object?
- Project nested entity into nested view model
- Join data from multiple tables into SelectList, and precede each dataset with a caption in MVC
- C# Join two tables using Include. Data comes from a Model
- I want to retrieve data from multiple tables to service payer into my project
- Join LINQ tables based on model definition
- Using LINQ, join data from tables into single rows in multi list form view
- Code throws Invalid cast exception: linq-sql join two tables
- project linq group by result into a single view model object?
- Concatenating the contents of a joined table and project into a view model in LINQ to SQL
- Linq - Join multiple tables and get output into single result set
- convert SQL query with multiple join on multiple tables using group by on multiple columns and with aggregate function into LINQ
- i am trying to join 3 tables below is the code
- How to perform Join between multiple tables in LINQ lambda
- LINQ to SQL multiple tables left outer join
- How To Project a Line Number Into Linq Query Results
- Select All columns for all tables in join + linq join
- lambda expression join multiple tables with select and where clause
- Joining three tables and using a left outer join
- How to join 3 tables with lambda expression?
- How to join tables in EF LINQ
- Split datatable into multiple fixed sized tables
- How to inner join tables from different Data Context?
- How to join 3 tables with linq
- How to left join multiple tables with LINQ
- Join tables in SQLite.Net with LINQ on xamarin.android is not supported
- Multiple tables left join using Linq
- EF Combine several tables into one IQueryable
- How to convert this LEFT JOIN Linq Query to Lambda expression
- NHibernate List - Unable to cast object of type System.Collections.Hashtable
- LINQ to Data : Clever Type Recognition
- Can you use LINQ extension method operators in an ASP.NET databinding expression?
- call a function for each value in a generic c# collection
- Maybe a Really simple, Dynamic Linq To Entities Select statement?
- XML LINQ statement almost giving desired results
- Changing Combobox should update entity EF6
- Entity Framework 4 generated queries are joining full tables
- Linq group by + where for each group
- How to read string array value using linq where clause instead of foreach loop
- LINQ query for ordering by a specific set list
- Sorting Dictionary by Value C#
- Linq Join with GroupBy and Sum
- How to get distinct values of each property independently
- Use DataContractSerializer to Deserialize only part of an XML document
- how to allow the user to choose how to sort his list using a variable?
- How to keep initializer list order within Select and/or SelectMany
- EF Core : Must be reducable node
- Linq Group by Week Number Including 0 Record Weeks