更新时间:2023-02-24 14:31:24
我做在初始LINQ查询的排序依据,那么,为什么抱怨,我需要做一个跳过前做一个排序依据(我想我是?)
块引用>您
结果
正确地开始了作为一个有序可查询:从查询的第一行是返回的类型IOrderedQueryable< ProviderWithDistance>
,因为你必须用子句的订单。然而,加入
其中,
在它的上面,使您的查询一个普通的的IQueryable< ProviderWithDistance>
再次,造成问题你看到的道路。从逻辑上讲,这是同样的事情,但在内存中的查询定义的结构以其他方式暗示。要解决这个问题,通过删除
序code>在原来的查询,并添加你是对之前准备好分页,像这样的:
。 ..
如果(string.IsNullOrEmpty(语言)==假)
:
如果(string.IsNullOrEmpty(关键字)==假)
...
结果= result.OrderBy(R = GT; r.distance);
只要顺序是上一次的操作,这应该可以解决运行时的问题。
我的假设下,它不会变成一个SQL查询和执行,直到我列举的结果,这就是为什么我等到最后一行返回结果
AsEnumerable()
。那是正确的做法?
块引用>是的,这是正确的做法。您希望您的RDBMS做尽可能多的工作越好,因为这样做在内存分页失败摆在首位分页的目的。
如果我在做之前跳过枚举结果并采取将如何影响性能?
块引用>这会杀死的性能,因为你的系统会需要走动更大量的数据的确要比您添加分页之前。
Using Entity Framework 6.0.2 and .NET 4.5.1 in Visual Studio 2013 Update 1 with a
DbContext
connected to SQL Server:I have a long chain of filters I am applying to a query based on the caller's desired results. Everything was fine until I needed to add paging. Here's a glimpse:
IQueryable<ProviderWithDistance> results = (from pl in db.ProviderLocations let distance = pl.Location.Geocode.Distance(_geo) where pl.Location.Geocode.IsEmpty == false where distance <= radius * 1609.344 orderby distance select new ProviderWithDistance() { Provider = pl.Provider, Distance = Math.Round((double)(distance / 1609.344), 1) }).Distinct(); if (gender != null) { results = results.Where(p => p.Provider.Gender == (gender.ToUpper() == "M" ? Gender.Male : Gender.Female)); } if (type != null) { int providerType; if (int.TryParse(type, out providerType)) results = results.Where(p => p.Provider.ProviderType.Id == providerType); } if (newpatients != null && newpatients == true) { results = results.Where(p => p.Provider.ProviderLocations.Any(pl => pl.AcceptingNewPatients == null || pl.AcceptingNewPatients == AcceptingNewPatients.Yes)); } if (string.IsNullOrEmpty(specialties) == false) { List<int> _ids = specialties.Split(',').Select(int.Parse).ToList(); results = results.Where(p => p.Provider.Specialties.Any(x => _ids.Contains(x.Id))); } if (string.IsNullOrEmpty(degrees) == false) { List<int> _ids = specialties.Split(',').Select(int.Parse).ToList(); results = results.Where(p => p.Provider.Degrees.Any(x => _ids.Contains(x.Id))); } if (string.IsNullOrEmpty(languages) == false) { List<int> _ids = specialties.Split(',').Select(int.Parse).ToList(); results = results.Where(p => p.Provider.Languages.Any(x => _ids.Contains(x.Id))); } if (string.IsNullOrEmpty(keyword) == false) { results = results.Where(p => (p.Provider.FirstName + " " + p.Provider.LastName).Contains(keyword)); }
Here's the paging I added to the bottom (
skip
andmax
are just int parameters):if (skip > 0) results = results.Skip(skip); results = results.Take(max); return new ProviderWithDistanceDto { Locations = results.AsEnumerable() };
Now for my question(s):
As you can see, I am doing an orderby in the initial LINQ query, so why is it complaining that I need to do an
OrderBy
before doing aSkip
(I thought I was?)...I was under the assumption that it won't be turned into a SQL query and executed until I enumerate the results, which is why I wait until the last line to return the results AsEnumerable(). Is that the correct approach?
If I have to enumerate the results before doing
Skip
andTake
how will that affect performance? Obviously I'd like to have SQL Server do the heavy lifting and return only the requested results. Or does it not matter (or have I got it wrong)?
I am doing an orderby in the initial LINQ query, so why is it complaining that I need to do an OrderBy before doing a Skip (I thought I was?)
Your
result
starts off correctly as an ordered queryable: the type returned from the query on the first line isIOrderedQueryable<ProviderWithDistance>
, because you have anorder by
clause. However, adding aWhere
on top of it makes your query an ordinaryIQueryable<ProviderWithDistance>
again, causing the problem that you see down the road. Logically, that's the same thing, but the structure of the query definition in memory implies otherwise.To fix this, remove the
order by
in the original query, and add it right before you are ready for the paging, like this:... if (string.IsNullOrEmpty(languages) == false) ... if (string.IsNullOrEmpty(keyword) == false) ... result = result.OrderBy(r => r.distance);
As long as ordering is the last operation, this should fix the runtime problem.
I was under the assumption that it won't be turned into a SQL query and executed until I enumerate the results, which is why I wait until the last line to return the results
AsEnumerable()
. Is that the correct approach?Yes, that is the correct approach. You want your RDBMS to do as much work as possible, because doing paging in memory defeats the purpose of paging in the first place.
If I have to enumerate the results before doing Skip and Take how will that affect performance?
It would kill the performance, because your system would need to move around a lot more data than it did before you added paging.