我需要优化此Linq查询
我有一个需要优化的Linq查询。我需要优化此Linq查询
编辑
我想对所有我所试图做的是选择在第一个表中的所有行，但选取的调查，CB和科学表的最后或默认的行...
编辑
当这将创建一个查询，我打一次数据库整个查询，然后再一次为每一个结果我得到第一个查询回来。因此，如果TestClass返回30个结果，那么我会再次为调查对象点击数据库30次。
我真的需要一个更好的方式来完成这项工作。
在此先感谢！
实例查询
return (from xx in db.test
select new TestClass {
Added_By_User_ID = xx.Added_By_User_ID,
Survey = (from ru in db.Utopia
where ru.Province_ID == xx.Province_ID
orderby ru.uid descending
select ru).Take(1).ToList(),
}).ToList();
房地产查询
return (from xx in db.Utopia_Province_Data_Captured_Gens
where xx.Owner_Kingdom_ID == ownerKingdomID
where kingdomList.Contains((Guid)xx.Kingdom_ID)
select new ProvinceClass
{
Kingdom_ID = xx.Kingdom_ID,
Kingdom_Island = xx.Kingdom_Island,
Kingdom_Location = xx.Kingdom_Location,
Owner_Kingdom_ID = xx.Owner_Kingdom_ID,
Province_ID = xx.Province_ID,
Province_Name = xx.Province_Name,
Owner_User_ID = xx.Owner_User_ID,
Race_ID = xx.Race_ID,
Updated_By_DateTime = xx.Updated_By_DateTime,
Networth = xx.Networth,
Land = xx.Land,
Monarch_Display = xx.Monarch_Display,
Owner = xx.Owner,
Sub_Monarch = xx.Sub_Monarch,
CB_Updated_By_Province_ID = xx.CB_Updated_By_Province_ID,
uid = xx.uid,
Formatted_By = xx.Formatted_By,
Utopian_Day_Month = xx.Utopian_Day_Month,
Utopian_Year = xx.Utopian_Year,
Ruler_Name = xx.Ruler_Name,
Personality_ID = xx.Personality_ID,
Nobility_ID = xx.Nobility_ID,
Money = xx.Money,
Daily_Income = xx.Daily_Income,
Food = xx.Food,
Runes = xx.Runes,
Population = xx.Population,
Peasents = xx.Peasents,
Peasents_Non_Percentage = xx.Peasents_Non_Percentage,
Trade_Balance = xx.Trade_Balance,
Building_Effectiveness = xx.Building_Effectiveness,
Military_Efficiency_Off = xx.Military_Efficiency_Off,
Military_Efficiency_Def = xx.Military_Efficiency_Def,
Draft = xx.Draft,
Soldiers = xx.Soldiers,
Soldiers_Regs_Off = xx.Soldiers_Regs_Off,
Soldiers_Regs_Def = xx.Soldiers_Regs_Def,
Soldiers_Elites = xx.Soldiers_Elites,
War_Horses = xx.War_Horses,
//*ers = xx.*ers,
Military_Net_Off = xx.Military_Net_Off,
Military_Net_Def = xx.Military_Net_Def,
Military_Current_Off = xx.Military_Current_Off,
Military_Current_Def = xx.Military_Current_Def,
Mil_Training = xx.Mil_Training,
Mil_Wage = xx.Mil_Wage,
Mil_Overall_Efficiency = xx.Mil_Overall_Efficiency,
Mil_Total_Generals = xx.Mil_Total_Generals,
Wizards = xx.Wizards,
Wizards_Value_Type = xx.Wizards_Value_Type,
Thieves = xx.Thieves,
Thieves_Value_Type = xx.Thieves_Value_Type,
Plague = xx.Plague,
Monarch_Vote_Province_ID = xx.Monarch_Vote_Province_ID,
Protected = xx.Protected,
Hit = xx.Hit,
Honor = xx.Honor,
Province_Notes = xx.Province_Notes,
CB_Export_Line = xx.CB_Export_Line,
Army_Out = xx.Army_Out,
Army_Out_Expires = xx.Army_Out_Expires,
Updated_By_Province_ID = xx.Updated_By_Province_ID,
SOM_Updated_By_Province_ID = xx.SOM_Updated_By_Province_ID,
SOM_Updated_By_DateTime = xx.SOM_Updated_By_DateTime,
CB_Updated_By_DateTime = xx.CB_Updated_By_DateTime,
CB_Requested = xx.CB_Requested,
CB_Requested_Province_ID = xx.CB_Requested_Province_ID,
SOM_Requested = xx.SOM_Requested,
SOM_Requested_Province_ID = xx.SOM_Requested_Province_ID,
SOS_Requested = xx.SOS_Requested,
SOS_Requested_Province_ID = xx.SOS_Requested_Province_ID,
Survey_Requested = xx.Survey_Requested,
Survey_Requested_Province_ID = xx.Survey_Requested_Province_ID,
Last_Login_For_Province = xx.Last_Login_For_Province,
Date_Time_User_ID_Linked = xx.Date_Time_User_ID_Linked,
Added_By_User_ID = xx.Added_By_User_ID,
NoteCount = (from yy in db.Utopia_Province_Notes
where yy.Province_ID == xx.Province_ID
select yy).Count(),
SOM = (from uu in db.Utopia_Province_Data_Captured_Type_Militaries
where uu.Province_ID == xx.Province_ID
where uu.Owner_Kingdom_ID == ownerKingdomID
where uu.DateTime_Added == (from ru in db.Utopia_Province_Data_Captured_Type_Militaries //datetime can be same for multiple items.
where ru.Province_ID == xx.Province_ID
where ru.Owner_Kingdom_ID == ownerKingdomID
orderby ru.uid descending // To get the last most inserted rows
select ru.DateTime_Added).FirstOrDefault()
select uu).ToList(),
SOS = (from zz in db.Utopia_Province_Data_Captured_Sciences
where ru.Province_ID == xx.Province_ID
where ru.Owner_Kingdom_ID == ownerKingdomID
orderby ru.uid descending
select ru).Take(1).ToList(),
Survey = (from ru in db.Utopia_Province_Data_Captured_Surveys
where ru.Province_ID == xx.Province_ID
where ru.Owner_Kingdom_ID == ownerKingdomID
orderby ru.uid descending
select ru).Take(1).ToList(),
CB = (from ru in db.Utopia_Province_Data_Captured_CBs
where ru.Province_ID == xx.Province_ID
where ru.Owner_Kingdom_ID == ownerKingdomID
orderby ru.uid descending
select ru).Take(1).ToList()
}).ToList();
您可以从您的乌托邦表中选择并加入第二个用于获取USER_ID，如：
from ru in db.Utopia
join xx in test on ru.Province_ID equals xx.Province_ID
orderby ru.uid descending
select new TestClass
{
Added_By_User_ID = xx.Added_By_User_ID,
Survey = ru
}
更新的真正查询
这是一个巨大的查询，你应该考虑一种基于连接的方法，正如我建议的那样，并且基于Jim McKeeth提出的本地缓存。或者你应该一起创建一个新的表格。
当你需要从可选的数据多表查询，您可以执行左连接：
from ru in db.Utopia
join user in db.Users on ru.UserId equals user.Id // User is required
join survey in db.Surveys on ru.Province_ID equals survey.Province_ID into j1
from survey in j1.DefaultIfEmpty() // Survey is optional (left join)
join address in db.Address on ru.UserId equals address.UserId into j2
from survey in j2.DefaultIfEmpty() // Address is optional (left join)
orderby ru.uid descending
select new TestClass
{
Added_By_User_ID = xx.Added_By_User_ID,
Survey = survey,
Street = address.Street,
UserName = new UserName
{
FirstName = user.FirstName,
LastName = user.LastName
}
}
注意的量加入会影响您的查询的性能。迟早会用高速缓存找到更好的方法会变得更高效。
另一个需要注意的是，在查询中调用ToList（）将实际执行该部分查询（或整个查询）。
喜欢的东西：
from xx in db.test
join ru in db.Utopia on xx.Province_ID equals ru.Province_ID
orderby ru.uid descending
select new ..
如何获得最后一个完整的RU表格？正如你所看到的，我想要一个全新的对象的整个调查... –
你真正的查询的复杂性给我指示，你应该提出这个LINQ语句转换成一个存储过程，然后调用该代替。我建议这样做，因为（a）您将完全控制查询的执行方式，并且（b）在TSQL中分析和性能优化您的查询更容易。这是我们过去在大型项目中使用的策略，当时结果集的生成变得不平凡。
我也要建议你使用AutoMapper执行存储过程的结果转移到你的DTO的任务，因为你似乎有接近1：1映射
视图也将是一个合适的包装。你可以加入视图。不确定您是否可以将sproc结果加入临时表中。 –
好点。我想我仍然会使用sproc，因为它可以返回多个结果集，并且可以轻松地将上面提到的内容列表中的内容提取出来。 我只能想象生成运行此查询的可笑的低效TSQL！ – geoffreys
LINQ2SQL就是我使用 –
你干得嵌套查询，所以你所看到的行为是可以预料的。如果您编写了类似的SQL，则会遇到同样的问题。 Polity建议，简单的嵌套查询可以用连接替代。 –
您可以通过让此表按id链接到这些表来消除“排序，取一个”查询。那么你有一个历史，但你只有一个“当前”价值。支持一个列表也是有点奇怪的，但是让这个列表每个只有一个值。我不知道Linq to SQL，但是在Linq to Entities中，可以使用'.Include（“SomeProperty”）'强制同时检索数据。这只适用于你有一个省份实体和适当的DB FK关系。 –