问题描述
在项目中我有这个表:
- 产品(ID,采用catalogId,manufacturerId ...)
- 目录
- 制造商
此外产品
模式(ID,名称,采用catalogId,catalogTitle,manufacturerId,manufacturerName)
。
如何能Linq中写低于这个SQL查询,如果我想获得产品项目?
SELECT Product.Name,Product.CatalogId ,Product.ManufacturerId,[录] .name和Manufacturer.Name
从产品,[目录],制造商
WHERE [目录] .ID = Product.CatalogId AND Manufacturer.id = Product.ManufacturerId和产品。主动= 1
首先,我会回答你问题..然后解决您的答案评论。要回答你的问题,Linq中你会做到以下几点:
由对在产品
将C在目录上c.Id等于p.CatalogId
在m.Id厂商加入m等于p.ManufacturerId
,其中p.Active == 1
选择新的{名称= p.Name,采用catalogId = p .CatalogId,ManufacturerId = p.ManufacturerId,CatalogName编= c.Name,ManufacturerName = m.Name};
这会给你和你所要求的项目一个匿名对象。如果你需要在其他地方使用它(和你不使用动态对象),我建议创建一个视图模型,并实例那些在您的选择之一。
例如:
公共类ProductInfoView
{
公共字符串名称{;组; }
公众诠释采用catalogId {搞定;组; }
公众诠释ManufacturerId {搞定;组; }
公共字符串CatalogName编{搞定;组; }
公共字符串ManufacturerName {搞定;组; }
}
$ B $从产品
P B在c.Id目录加入c等于p.CatalogId
在制造商加入M于m.Id等于p.ManufacturerId
,其中p.Active == 1
选择新ProductInfoView(){名称= p.Name,采用catalogId = p.CatalogId,ManufacturerId = p.ManufacturerId,CatalogName编= c.Name,ManufacturerName = m.Name};
这将使引用查询结果少一些痛苦。
要回答你的评论,你做了很多,如果你想要的是产品的连接。您的标准只能保证三件事
- 您的产品的活动标志是1
- 您的产品有现有的目录条目
- 您的产品有一个现有的制造商条目
如果#2,#3是多余的,你不一定需要的名字,你可以简单地做:
从产品
,其中p p.Active == 1
的选择p
如果产品是一个CRUD模型,你可以潜在的深加载它,包括制造商/目录信息,或者使用上述视图模型。
祝你好运!
In project I have this tables:
- Product(id,catalogId, manufacturerId...)
- Catalog
- Manufacturer
Also Product
model (id, name, catalogId, catalogTitle, manufacturerId, manufacturerName)
.
How can write in Linq this SQL query below if I want get Product item?
SELECT Product.Name, Product.CatalogId, Product.ManufacturerId, [Catalog].Name, Manufacturer.Name
FROM Product, [Catalog], Manufacturer
WHERE [Catalog].Id=Product.CatalogId AND Manufacturer.id=Product.ManufacturerId AND Product.Active=1
First, I'll answer your question.. then address your answer to comments. To answer your question, in Linq you would do the following:
from p in Product
join c in Catalog on c.Id equals p.CatalogId
join m in Manufacturer on m.Id equals p.ManufacturerId
where p.Active == 1
select new { Name = p.Name, CatalogId = p.CatalogId, ManufacturerId = p.ManufacturerId, CatalogName = c.Name, ManufacturerName = m.Name };
This will give you an anonymous object with the items you requested. If you need to use this elsewhere (and you're not using dynamic objects), I would suggest creating a view-model, and instantiating one of those in your select.
Example:
public class ProductInfoView
{
public string Name { get; set; }
public int CatalogId { get; set; }
public int ManufacturerId { get; set; }
public string CatalogName { get; set; }
public string ManufacturerName { get; set; }
}
from p in Product
join c in Catalog on c.Id equals p.CatalogId
join m in Manufacturer on m.Id equals p.ManufacturerId
where p.Active == 1
select new ProductInfoView() { Name = p.Name, CatalogId = p.CatalogId, ManufacturerId = p.ManufacturerId, CatalogName = c.Name, ManufacturerName = m.Name };
This will make referencing your query results a little less painful.
To answer your comment, you're doing a lot of joins if all you want is the product. Your criteria will only ensure three things
- Your product's Active flag is 1
- Your product has an existing Catalog entry
- Your product has an existing Manufacturer entry
If #2 and #3 are superfluous and you don't necessarily need the names, you could simply do:
from p in Product
where p.Active == 1
select p
If Product is a CRUD model, you could potentially deep-load it to include Manufacturer/Catalog information, or use the aforementioned view-model.
Good luck!
这篇关于LINQ。从多个表中选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!