带有 WHERE 子句的 SQL INNER JOIN 到 LINQ 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8737352/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL INNER JOIN with WHERE clause to LINQ format
提问by inquisitive_one
How would I convert this sql query to LINQ?
我将如何将此 sql 查询转换为 LINQ？
SELECT company.ticker, company.primary_analyst,
personnel.last_name, company.research_associate,
company.secondary_associate, company.coverage_status
FROM company
INNER JOIN personnel ON company.primary_analyst = personnel.dpinitials
WHERE personnel.last_name='marley' AND company.associate='ml'
ORDER BY company.coverage_status
回答by BrokenGlass
It's pretty similar:
它非常相似：
var results = from c in company
join p in personnel on c.primary_analyst equals p.dpinitals
where p.last_name == 'marley' and c.associate == 'ml'
orderby c.coverage_status asc
select new
{
c.ticker, c.primary_analyst, p.last_name, c.research_associate,
c.secondary_associate, c.coverage_status
};
Above projects to an anonymous class with the properties you want - if you have an equivalent POCO class in your model you should project to that, if not in many cases you probably should create one.
以上项目到具有您想要的属性的匿名类 - 如果您的模型中有一个等效的 POCO 类，您应该投影到该类，如果不是在很多情况下，您可能应该创建一个。
回答by Nils Magne Lunde
The solution from @BrokenGlass is perfectly fine. However, if you have a 1..many relationship it is rarely necessary to use the join operator in LINQ. In this example, if company->personell was 1..many, I would write the query like this:
@BrokenGlass 的解决方案非常好。但是，如果您有 1..many 关系，则很少需要在 LINQ 中使用连接运算符。在这个例子中，如果 company->personell 是 1..many，我会写这样的查询：
var results = from c in company
where c.associate == "ml"
from p in c.personnel
where p.last_name == "marley"
orderby c.coverage_status asc
select new
{
c.ticker,
c.primary_analyst,
p.last_name,
c.research_associate,
c.secondary_associate,
c.coverage_status
};
This can also be written using the expression chain syntax:
这也可以使用表达式链语法编写：
var results = company.Where(c => c.associate == "ml")
.SelectMany(c => c.personnel, (c, p) => new
{
c.ticker,
c.primary_analyst,
p.last_name,
c.research_associate,
c.secondary_associate,
c.coverage_status
})
.Where(x => x.last_name == "marley")
.OrderBy(x => x.coverage_status)
回答by Sarfraj Sutar
var list = (from u in db.Users
join c in db.Customers on u.CustomerId equals c.CustomerId
where u.Username == username
select new {u.UserId, u.CustomerId, u.ClientId, u.RoleId, u.Username, u.Email, u.Password, u.Salt, u.Hint1, u.Hint2, u.Hint3, u.Locked, u.Active,c.ProfilePic}).First();
