blocks|key|194963|text|//var+Country+=+Countries.FirstOrDefault(o+=>+o.Id+==+100000581);
var+personnelIds+=+context.Personnels
++++.Where(p+=>+p.Country.Id+==+100000581)
++++.Select(p+=>+p.Id)
++++.ToArray();

personnelIds.Dump();|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|194964|试试这个，应该会更好。|unstyled|194965|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>//var Country = Countries.FirstOrDefault(o =&gt; o.Id == 100000581);
var personnelIds = context.Personnels
 .Where(p =&gt; p.Country.Id == 100000581)
 .Select(p =&gt; p.Id)
 .ToArray();

personnelIds.Dump();
</code></pre>

Try this, it should be better.

blocks|key|3028677|text|Try还可以尝试将人员分组到单个查询中|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3028678|var+groups+=
++++(from+p+in+Personnel
+++++group+p+by+p.CountryId+into+g
+++++select+new+
+++++{
+++++++++CountryId+=+g.Key
+++++++++PersonnelIds+=+p.Select(x+=>+x.Id)
+++++});
var+personnelIds+=+groups.FirstOrDefault(g+=>+g.Key+==+100000581);|code-block|syntax|javascript|3028679|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try can also try grouping personnel into a single query

<pre><code>var groups =
 (from p in Personnel
 group p by p.CountryId into g
 select new 
 {
 CountryId = g.Key
 PersonnelIds = p.Select(x =&gt; x.Id)
 });
var personnelIds = groups.FirstOrDefault(g =&gt; g.Key == 100000581);
</code></pre>

blocks|key|192584|text|您是否在POCO中为人员明确定义了ForeignKey？在EF中省略它是很常见的，但是添加它将极大地简化这段代码和生成的SQL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|192585|public+class+Personnel
{
++++public+Country+Country+{+get;+set;+}

++++[ForeignKey("Country")]
++++public+int+CountryId+{+get;+set;+}

++++.+.+.
}

>+update-database+-f+-verbose

var+ids+=+db.Personnel.Where(p+=>+p.CountryId+==+100000581).Select(p+=>+p.Id).ToArray();|code-block|syntax|javascript|192586|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Do you have the ForeignKey explicitly defined in your POCO for Personnel? It's common to leave it out in EF, but adding it would massively simplify both this code and the resulting SQL:

<pre><code>public class Personnel
{
 public Country Country { get; set; }

 [ForeignKey("Country")]
 public int CountryId { get; set; }

 . . .
}

&gt; update-database -f -verbose

var ids = db.Personnel.Where(p =&gt; p.CountryId == 100000581).Select(p =&gt; p.Id).ToArray();
</code></pre>

blocks|key|2816278|text|当被访问时，Personnels集合将通过惰性加载进行填充，从而从DB中检索所有字段。事情是这样的.|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2816279|//+retrieves+data+and+builds+the+single+Country+entity+(if+not+null+result)
var+Country+=+Countries.FirstOrDefault(o+=>+o.Id+==+100000581);

//+Country.Personnels+accessor+will+lazy+load+and+construct+all+Personnel+entity+objects+related+to+this+country+entity+object
//+hence+loading+all+of+the+fields
var+personnelIds+=+Country.Personnels.Select(p+=>+p.Id).ToArray();|code-block|syntax|javascript|2816280|你想要更多像这样的东西：|2816281|//+build+base+query+projecting+desired+data
var+personnelIdsQuery+=+dbContext.Countries
++++.Where(+c+=>+c.Id+==+100000581+)
++++.Select(+c+=>+new
++++++++{
++++++++++++CountryId+=+c.Id,
++++++++++++PersonnelIds+=+c.Personnels.Select(+p+=>+p.Id+)
++++++++}

//+now+do+enumeration
//+your+example+shows+FirstOrDefault+without+OrderBy
//+either+use+SingleOrDefault+or+specify+an+OrderBy+prior+to+using+FirstOrDefaul

var+result+=+personnelIdsQuery.OrderBy(+item+=>+item.CountryId+).FirstOrDefault();|2816282|或者：|2816283|var+result+=+personnelIdsQuery.SingleOrDefault();|2816284|如果不为空，则获取ID数组|2816285|if(+null+!=+result+)
{
++++var+personnelIds+=+result.PersonnelIds;
}|2816286|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Personnels collection will be populated via lazy loading when accessed, hence retrieving all of the fields from the DB. Here's what's happening...

<pre><code>// retrieves data and builds the single Country entity (if not null result)
var Country = Countries.FirstOrDefault(o =&gt; o.Id == 100000581);

// Country.Personnels accessor will lazy load and construct all Personnel entity objects related to this country entity object
// hence loading all of the fields
var personnelIds = Country.Personnels.Select(p =&gt; p.Id).ToArray();
</code></pre>

You want something more like this:

<pre><code>// build base query projecting desired data
var personnelIdsQuery = dbContext.Countries
 .Where( c =&gt; c.Id == 100000581 )
 .Select( c =&gt; new
 {
 CountryId = c.Id,
 PersonnelIds = c.Personnels.Select( p =&gt; p.Id )
 }

// now do enumeration
// your example shows FirstOrDefault without OrderBy
// either use SingleOrDefault or specify an OrderBy prior to using FirstOrDefaul

var result = personnelIdsQuery.OrderBy( item =&gt; item.CountryId ).FirstOrDefault();
</code></pre>

OR:

<pre><code>var result = personnelIdsQuery.SingleOrDefault();
</code></pre>

Then get the array of IDs if not null

<pre><code>if( null != result )
{
 var personnelIds = result.PersonnelIds;
}
</code></pre>

The EntityModel is defined as:
Personnel has a link to a Country

When executing this code in LinqPad, I see that the SQL which is generated is not optimized (all fields are returned) in the first query ? What am I missing here or doing wrong ?

Query 1 LINQ

<pre><code>var Country = Countries.FirstOrDefault(o =&gt; o.Id == 100000581);
var personnelIds = Country.Personnels.Select(p =&gt; p.Id).ToArray();
personnelIds.Dump();
</code></pre>

Query 1 SQL

<pre><code>exec sp_executesql N'SELECT [t0].[Id], [t0].[Version], [t0].[Identifier], [t0].[Name], , [t0].[UpdatedBy] FROM [Personnel] AS [t0] WHERE [t0].[Country_Id] = @p0',N'@p0 bigint',@p0=100000581
</code></pre>

 
 

Query 2 LINQ

<pre><code>var Country = Countries.FirstOrDefault(o =&gt; o.Id == 100000581);
var personnelIds2 = Personnels.Where(p =&gt; p.Country == Country).Select(p =&gt; p.Id).ToArray();
personnelIds2.Dump();
</code></pre>

Query 2 SQL

<pre><code>exec sp_executesql N'SELECT [t0].[Id] FROM [Personnel] AS [t0] WHERE [t0].[Country_Id] = @p0',N'@p0 bigint',@p0=100000581
</code></pre>

 
The database used is SQL Express 2008. And LinqPad version is 4.43.06

Select only a single column in LINQ

数据库

EntityModel的定义是: Personnel有一个国家/地区的链接当在LinqPad中执行这段代码时，我看到在第一个查询中生成的SQL没有优化(所有字段都被返回)？我在这里遗漏了什么或者做错了什么？查询1 LINQvar Country = Countries.FirstOrDefault(o => o.Id ...

问在LINQ中仅选择一列
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在LINQ中仅选择一列EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在LINQ中仅选择一列
EN