LINQ。从多个表中选择 [英] Linq. Select from multiple tables

问题描述

在项目中我有这个表：

1. 产品（ID，采用catalogId，manufacturerId ...）


2. 目录


3. 制造商

此外产品模式（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. 您的产品的活动标志是1


2. 您的产品有现有的目录条目


3. 您的产品有一个现有的制造商条目

如果＃2，＃3是多余的，你不一定需要的名字，你可以简单地做：

 从产品
，其中p p.Active == 1 
的选择p 
  

如果产品是一个CRUD模型，你可以潜在的深加载它，包括制造商/目录信息，或者使用上述视图模型。

祝你好运！

In project I have this tables:

1. Product(id,catalogId, manufacturerId...)
2. Catalog
3. 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

1. Your product's Active flag is 1
2. Your product has an existing Catalog entry
3. 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。从多个表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！