I have the following query I'm using (which mostly came from the help I received here):
我有以下我正在使用的查询(主要来自我在这里收到的帮助):
public IEnumerable<Entities.AuditAgency> GetAuditRuleAgencyRecords(IEnumerable<Entities.AuditRuleEnterprise> rules)
{
using (LinqModelDataContext db = new LinqModelDataContext())
{
// Left-Outer Joins on Agency and its various other tables.
var auditAgencyRecords = (from ag in db.Agencies
join ara in db.AuditRuleAccounts on ag.Agency_Id equals ara.AgencyID into aran
from ara in aran.DefaultIfEmpty()
join arr in db.AuditRuleResults on ara.AuditRuleAccountID equals arr.AuditRuleAccountID into arrn
from arr in arrn.DefaultIfEmpty()
join are in db.AuditRuleEnterprises on arr.AuditRuleEnterpriseID equals are.AuditRuleEnterpriseID into aren
from are in aren.DefaultIfEmpty()
select new
{
AgencyID = ag.Agency_Id,
AgencyName = ag.Agency_Name,
AuditRuleEnterpriseID = arr.AuditRuleEnterpriseID,
AuditRuleEnterpriseName = are.OverrideDisplayName,
CorrectedDate = arr.CorrectedDate,
NbrDaysToCorrect = arr.NbrDaysToCorrect,
});
IEnumerable<AuditAgency> AuditAgencies = auditAgencyRecords
.GroupBy(a => a.AgencyID)
.Select(ag => new AuditAgency()
{
AgencyID = ag.Key,
AgencyName = ag.First().AgencyName,
Rules = ag
.GroupBy(agr => agr.AuditRuleEnterpriseID)
// ----> Do a left outer join on parameter "rules" object and the returned group above
// ----> on both of their ID's
.Select(agrg => new AuditAgencyRule() // Now I would like to only be creating "rules" for the rules with IDs that match the rules passed into this method
{
AuditRuleID = agrg.Key,
AuditRuleName = agrg.First().AuditRuleEnterpriseName,
Days = (Int32)agrg.Average(agrgr => agrgr.NbrDaysToCorrect)
})
}).ToList();
return AuditAgencies;
}
This returns me a list of AuditAgency objects and each AuditAgency contains a list of AuditAgencyRules.
这将返回一个AuditAgency对象列表,每个AuditAgency包含一个AuditAgencyRules列表。
Now, as my final step in this query, and the part I'm having trouble with.. is, you can see that an IEnumerable rules is passed in as a parameter to this method.
现在,作为我在这个查询中的最后一步,以及我遇到麻烦的部分,你可以看到IEnumerable规则作为参数传递给这个方法。
What I would like to do, is in my second query there, where it creates a list of rules for each agency, I want to do a left outer join on my local "rules" object. Each rule object I'm passing in has a rule.ID. I want each agency to contain only the rules passed in, and if there is no data for them then simply leave its contents null.
我想做的是,在我的第二个查询中,它为每个代理创建一个规则列表,我想在我的本地“规则”对象上进行左外连接。我传入的每个规则对象都有一个rule.ID.我希望每个代理商只包含传入的规则,如果没有数据,则只需将其内容保留为空。
Right now, my query contains all rules returned from the database and their data. However, I need it to contain only the rules passed into the method, regardless of if it has a match with the rules returning from the database. So in other words, I need to do a left outer join on my local "rules" object with the rules I have in there now.
现在,我的查询包含从数据库返回的所有规则及其数据。但是,我需要它只包含传递给方法的规则,无论它是否与从数据库返回的规则匹配。换句话说,我需要在我的本地“规则”对象上使用我现在的规则进行左外连接。
You can see where I added comments to the above code to explain where and what I'm trying to do.
您可以看到我在上面的代码中添加注释的位置,以解释我正在尝试的位置和内容。
So, if "rules" only contains 3 rules with their ID's set, then this query will only return those 3 rules for each agency, regardless of whether there is data for those rules (its data will be null). Rather than return all rules for each agency, which is what it does now.
因此,如果“规则”仅包含3个带有ID设置的规则,那么此查询将仅为每个代理返回这3个规则,无论是否存在这些规则的数据(其数据将为空)。而不是为每个机构返回所有规则,这就是它现在所做的。
How do I write this left-outer join for a "local" object in my LINQ to SQL query?
如何在LINQ to SQL查询中为“本地”对象编写此左外连接?
Here is an attempt by me to do my left-outer-join on my "rules" object and the rules in my AuditAgencies after that second query above:
以下是我尝试在上面的第二个查询之后对我的“规则”对象和我的AuditAgencies中的规则进行左外连接:
foreach (var agency in AuditAgencies)
{
agency.Rules = from rule in rules
join lr in agency.Rules on rule.EnterpriseID equals lr.AuditRuleID into g
from lr in g.DefaultIfEmpty()
select new AuditAgencyRule
{
AuditRuleID = rule.EnterpriseID,
AuditRuleName = rule.Name,
Days = lr.Days,
Flagged = lr.Flagged,
PercentFlagged = lr.PercentFlagged
};
}
No dice. Now, instead of all the rules, I'm getting ~no~ rules. When all I want is to just get the rules I'm passing into this method.
没有骰子。现在,而不是所有的规则,我得到〜没有〜规则。当我想要的只是获得我正在传递给这种方法的规则。
Can someone point me in the right direction?
有人能指出我正确的方向吗?
3 个解决方案
#1
I can offer you a bit of meta-help. You should go grab LINQPad and start using .Dump() to see where you're going wrong (you can also look at the direct SQL translations as well).
我可以为你提供一些帮助。你应该去抓LINQPad并开始使用.Dump()来查看你出错的地方(你也可以查看直接的SQL翻译)。
#2
Basically, you do a left outer join with local objects in the same way you would do it with any other Linq source. The problem comes in when the provider may not understand what you're doing.
基本上,您使用与其他Linq源相同的方式对本地对象执行左外连接。当提供商可能不了解您正在做什么时,问题就出现了。
So, have you tried it the obvious way to see if it worked? If that doesn't work, then you may find it reasonable to simply return the results and filter out the undesired rules in a third Linq statement.
那么,你有没有尝试过这种明显的方法来看它是否有效?如果这不起作用,那么您可能会发现简单地返回结果并在第三个Linq语句中过滤掉不需要的规则是合理的。
#3
It could be due to a deferred query which has not returned yet, especially considering your using IEnumeratable<>... check out Charlie Calvert's blog on the subject, to quickly check you can try slipping in something like a .ToArray() assignment which will cause the query to execute immediately.
这可能是由于一个尚未返回的延迟查询,特别是考虑到你使用IEnumeratable <> ...查看Charlie Calvert关于这个主题的博客,快速检查你可以尝试滑动类似.ToArray()的任务。将导致查询立即执行。
#1
I can offer you a bit of meta-help. You should go grab LINQPad and start using .Dump() to see where you're going wrong (you can also look at the direct SQL translations as well).
我可以为你提供一些帮助。你应该去抓LINQPad并开始使用.Dump()来查看你出错的地方(你也可以查看直接的SQL翻译)。
#2
Basically, you do a left outer join with local objects in the same way you would do it with any other Linq source. The problem comes in when the provider may not understand what you're doing.
基本上,您使用与其他Linq源相同的方式对本地对象执行左外连接。当提供商可能不了解您正在做什么时,问题就出现了。
So, have you tried it the obvious way to see if it worked? If that doesn't work, then you may find it reasonable to simply return the results and filter out the undesired rules in a third Linq statement.
那么,你有没有尝试过这种明显的方法来看它是否有效?如果这不起作用,那么您可能会发现简单地返回结果并在第三个Linq语句中过滤掉不需要的规则是合理的。
#3
It could be due to a deferred query which has not returned yet, especially considering your using IEnumeratable<>... check out Charlie Calvert's blog on the subject, to quickly check you can try slipping in something like a .ToArray() assignment which will cause the query to execute immediately.
这可能是由于一个尚未返回的延迟查询,特别是考虑到你使用IEnumeratable <> ...查看Charlie Calvert关于这个主题的博客,快速检查你可以尝试滑动类似.ToArray()的任务。将导致查询立即执行。