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 Query
- 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?
- LINQ method to join every n elements in a series by a function, and then re-aggregate those results into a new list?
- How do I prevent inserting new records into tables while using Linq and SQL?
- What's the best way to copy and insert data from related tables into a new set of related tables with Linq
- Join data from multiple tables into SelectList, and precede each dataset with a caption in MVC
- Concatenating the contents of a joined table and project into a view model in LINQ to SQL
- Use LINQ to omit some entries in the value part of a dictionary and project this into a new dictonary maintaing original keys
- 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
- lambda expression join multiple tables with select and where clause
- Joining three tables and using a left outer join
- Join two tables using linq, and fill a Dictionary of them
- linq query to join two tables and get the count from one table values from the other
- LINQ: Join MySql and SQL Server tables
- LINQ to SQL and Join two tables with OR clause
- LINQ to Entities, join two tables, then group and take sums of columns from both tables
- C# + LINQ + ADO.NET EF , join 2 tables and return everything without specifying all fields manually
- LINQ to SQL join 3 tables and select multiple columns and also using Sum
- How can I code an outer join using LINQ and EF6?
- Create a Dynamic Linq to EF Expression to Select IQueryable into new class and assign properties
- Linq join 2 datatables that share a column and put result in new datatable
- C# Linq Join 2 tables on multiple columns and GROUP BY for count
- linq select sum and average into view model
- Join multiple tables and single output using Entity Framework mvc5
- LINQ Project properties into a new anonymous type with Contains
- LINQ - GroupBy and project to a new type?
- LINQ Query To Join Two Tables and Select Most Recent Records from Table B corresponding to Table A
- Entity Framework: How to perform left join with EF and LINQ among multiple tables
More Query from same tag
- Help with this Linq query (many-to-many join)
- How to change the default OrderBy functionality so that it orders by a given value first?
- LINQ and xml data
- How do I return a element using LINQ query from an ObservableCollection?
- When does EF Core LINQ query result in null coalescing despite SingleOrDefault() would return null?
- Group by DateTime.Date in Linq to SQL with EF 4.1
- Override default sort in LINQ query using Array as Comparer
- JOIN XML Documents on Value using LINQ
- How to compare elements in an array in C#?
- Need help understanding how to convert sql statement to (Linq) or (Linq To SQL)
- How to remove items using linq
- Linq no-noes - the catch all sql-like select?
- Using DbFunctions.TruncateDate and still have error: **The specified type member 'Date' is not supported in LINQ to Entities.**
- Where returns wrong record
- Subquery in a Lambda Expression or LINQ
- Group elements by an array/Collection<T>
- how to get the row value of each column from linq
- Linq to XML query nested elements
- Linq-to-SQL combobox binding
- Get root node or nodes in adjacency datatable using VB.Net LINQ
- c# Randomly order a list when values are the same
- LINQ Joins - Performance
- Composite C1 How would I rewrite this Sql update statement to work in c#?
- How to set Filter Values in LinQ with SQL Query Text?
- GroupBy with Count
- LINQ: How to convert a composite LINQ result to dictionary
- using String.Split in a LINQ Where clause not working
- How would you refactor this LINQ code?
- Agregate rows with two or more columns with same value
- Mapping values of grouped query?