blocks|key|316925|text|你可以试试这样的东西。这将只返回一列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|316926|var+result+=+Context.paf_wgs84.Where(c+=>+c.Postcode.Contains(postcode)).Select(x=>new+{x.Postcode}).Take(15);|code-block|syntax|javascript|316927|生成的Sql语句将如下所示。|316928|/*
--+Region+Parameters
DECLARE+@p0+VarChar(1000)+=+'%25s%25'
--+EndRegion
SELECT+TOP+(15)+[t0].[code]
FROM+[paf_wgs84]+AS+[t0]
WHERE+[t0].[code]+LIKE+@p0
*/|316929|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can try something like this. This will only return one column.

<pre><code>var result = Context.paf_wgs84.Where(c =&gt; c.Postcode.Contains(postcode)).Select(x=&gt;new {x.Postcode}).Take(15);
</code></pre>

The generated Sql statement will look like this.

<pre><code>/*
-- Region Parameters
DECLARE @p0 VarChar(1000) = '%s%'
-- EndRegion
SELECT TOP (15) [t0].[code]
FROM [paf_wgs84] AS [t0]
WHERE [t0].[code] LIKE @p0
*/
</code></pre>

blocks|key|4068187|text|问题可能是statemnt中的contains方法没有被映射到sql中的like语句，而您最终将所有行都映射到sql中，然后在web层中执行contains搜索，而不是在数据库中执行相同的操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4068188|使用SqlMethods实现相同的目的..如下所示：|4068189|SqlMethods.Like(c.Postcode,+string.Format("%25{0}%25",postcode));|code-block|syntax|javascript|4068190|有时，您还可以使用字符串方法，如:+String.StartsWith或String.Ends，但在这种情况下，您不能..|4068191|以%25开头的类同样的子句很少是一个好主意-尤其是，它不能有效地利用任何索引。使用“全文搜索”可能会有更好的性能；但这不能通过LINQ直接获得|4068192|希望这对你的问题有所帮助。|4068193|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

the problem might be that your contains method in the statemnt it is not being mapped to a like statement in sql and you end up getting all the rows into sql and then doing a cotains search in your web tier instead of doing the same in your DB.

Use SqlMethods for the same.. somethig as follows:

<pre><code>SqlMethods.Like(c.Postcode, string.Format("%{0}%",postcode));
</code></pre>

sometimes you can also use the string methods like: String.StartsWith or String.Ends with but in this you cant..

Also - LIKE clauses starting % are rarely a good idea - not least, it can't make effective use of any index. You might have better performance using "full text search"; but this isn't directly available via LINQ

hope that helps your problem.

blocks|key|3783005|text|我把语句改成了|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3783006|var+result+=+Context.paf_wgs84.Where(c+=>+c.Postcode.StartsWith(postcode)).Take(10).Select(c+=>+c.Postcode).ToArray();|code-block|syntax|javascript|3783007|我使用foreach来添加字符串数组--我想这就是我浪费时间的地方！|3783008|不过，还是要感谢大家的帮助。|3783009|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I altered the statement to 

<pre><code>var result = Context.paf_wgs84.Where(c =&gt; c.Postcode.StartsWith(postcode)).Take(10).Select(c =&gt; c.Postcode).ToArray();
</code></pre>

I used a foreach look to add to a string array - I think this is where I was losing time!

Thanks for everyones help though.

blocks|key|2169358|text|使用新的FETCH和OFFSET语句，这些类型的查询的性能在SQL+Server+2012中得到了极大的提高(尽管到目前为止我还没有看到很多基准测试数据...)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2169359|问题是EF还没有利用这些新特性。|2169360|一种可能的解决方案是使用SQL+Server+2012并编写一个带有FETCH/OFFSET的存储过程，然后使用EF将其作为目标。这绝对不是一个短期的解决方案，但我想指出这个选项。|2169361|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

The performance of these types of queries has been greatly improved in SQL Server 2012 using the new FETCH and OFFSET statements (although I haven't seen much benchmarking data to date...). 

The trouble is EF doesnt utilize these new features yet. 

A possible solution is to use SQL Server 2012 and write a sproc with FETCH/OFFSET and then target this using EF. Definitely not a short term solution but I wanted to point out this option.

I wonder if someone can help me with this. I have a LINQ query - no problems with it, but it takes far too long to return data

<pre><code>var result = Context.paf_wgs84.Where(c =&gt; c.Postcode.Contains(postcode)).Take(15);
</code></pre>

Very simple, the idea as the user is typing, using AJAX, it returns a set of 15 possible matches.

The problem is, there are 1.6 million records

Running the following code in management studio takes around 3 seconds 

<pre><code>SELECT code
FROM paf_wgs84
WHERE (code LIKE '%EC1%')
</code></pre>

where as running the following code takes less than a second

<pre><code>SELECT TOP 15 code
FROM paf_wgs84
WHERE (code LIKE '%EC1%')
</code></pre>

Is there a way of doing something similar in LINQ without using <code>.take()</code>?

Linq query taking too long

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想知道有没有人能帮我一下。我有一个LINQ查询--没有问题，但返回数据的时间太长了var result = Context.paf_wgs84.Where(c => c.Postcode.Contains(postcode)).Take(15);非常简单，当用户使用AJAX输入时，它返回一组15个可能的匹配项。问题是...

问Linq查询花费的时间太长
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Linq查询花费的时间太长EN