实体框架未正确地将C＃转换为SQL [英] Entity Framework not correctly translating C# to SQL
问题描述
我一直在使用Linq to SQL并开始使用Entity Framework进行新项目。在将代码合并到Visual Studio中之前，我使用Linq Pad来测试我的代码。在VS中调试时，我注意到我的Counts不同。当我检查我的代码
在VS中创建的SQL时，我注意到它没有正确翻译。
我在VS中的代码：
var adviceLineCallTotalViewModelList = from a db.AdviceLineCalls
join a ag.AgencyNumber中的db.Agencies等于ag.AgencyNumber
join in in db a.StatusOfAdviceLineCallerID上的.StatusOfAdviceLineCallers等于st.StatusOfAdviceLineCallerID
在a.StaffID上的db.Staffs中的连接等于s.StaffID
在a.AdviceLineCallSubjectMatterID上的db.AdviceLineCallSubjectMatters中的连接sm等于sm.AdviceLineCallSubjectMatterID到gr p
来自sm_left in grp.DefaultIfEmpty（）
其中s.Employed == true
select new AdviceLineCallTotalViewModel（）
{
AdviceLineCallID = a.AdviceLineCallID，
AdviceLineCallSubjectMatterID = sm_left.AdviceLineCallSubjectMatterID，
AdviceLineCallSubjectMatterDesc = sm_left.AdviceLineCallSubjectMatterDesc，
StatusOfAdviceLineCallerID = st.StatusOfAdviceLineCallerID，
StatusOfAdviceLineCallerDesc = st.StatusOfAdviceLineCallerDesc，
AgencyNumber = a.AgencyNumber，
AgencyNumberNameFacility = ag.AgencyNumberNameFacility，
CallDate = a.CallDate，
CallLength = a.CallLength，
Comments = a.Comments，
StaffID = a .StaffID，
LastName = s.LastName
};
当我调试并查看生成的SQL时，我看到：
SELECT
[Extent1]。[AdviceLineCallID] AS [AdviceLineCallID]，
[Extent5]。[AdviceLineCallSubjectMatterID] AS [AdviceLineCallSubjectMatterID]，
[Extent5]。[AdviceLineCallSubjectMatterDesc] AS [AdviceLineCallSubjectMatterDesc]，
[Extent3]。[StatusOfAdviceLineCallerID] AS [StatusOfAdviceLineCallerID]，
[Extent3]。[StatusOfAdviceLineCallerDesc] AS [StatusOfAdviceLineCallerDesc]，
[ Extent1]。[AgencyNumber] AS [AgencyNumber]，
[Extent2]。[AgencyNumberNameFacility] AS [AgencyNumberNameFacility]，
[Extent1]。[CallDate] AS [CallDate]，
[Extent1]。[CallLength] AS [CallLength]，
[Extent1]。[Comments] AS [Comments]，
[Extent1]。[StaffID] AS [StaffID]，
[ Extent4]。[LastName] AS [LastName]
FROM [dbo]。[AdviceLineCall] AS [Extent1]
INNER JOIN [dbo]。[Agency] AS [Extent2] ON [Extent1]。[AgencyNumber] ] = [Extent2]。[AgencyNumber]
INNER JOIN [dbo]。[StatusOfAdviceLineCaller] AS [Extent3] ON [Extent1]。[StatusOfAdviceLineCallerID] = [Extent3]。[StatusOfAdviceLineCallerID]
INNER JOIN [dbo] ] [人员] AS [Extent4] ON [Extent1]。[StaffID] = [Extent4]。[StaffID]
INNER JOIN [dbo]。[AdviceLineCallSubjectMatter] AS [Extent5] ON [Extent1]。[AdviceLineCallSubjectMatterID] = [Extent5]。[AdviceLineCallSubjectMatterID]
WHERE 1 = [Extent4]。[Employed]
最后一个"INNER JOIN"应该是一个"LEFT OUTER JOIN"，因为这些行：
join sm在a.AdviceLineCallSubjectMatterID上的db.AdviceLineCallSubjectMatters中将sm.AdviceLineCallSubjectMatterID等于grp
来自sm_left中的grp.DefaultIfEmpty（）
我创建了另一个Visual Studio项目，从数据库中添加了ADO.NET数据提供程序，Code First，并复制并粘贴了相同的代码，它运行正常。
我的C＃代码是正确的转换为SQL。最后一次连接被正确翻译成LEFT OUTER JOIN：
SELECT
[Extent1]。[AdviceLineCallID] AS [AdviceLineCallID]，
[Extent5]。[AdviceLineCallSubjectMatterID] AS [AdviceLineCallSubjectMatterID]，
[Extent5]。[AdviceLineCallSubjectMatterDesc] AS [AdviceLineCallSubjectMatterDesc]，
[Extent3]。[StatusOfAdviceLineCallerID] AS [StatusOfAdviceLineCallerID]，
[ Extent3]。[StatusOfAdviceLineCallerDesc] AS [StatusOfAdviceLineCallerDesc]，
[Extent1]。[AgencyNumber] AS [AgencyNumber]，
[Extent2]。[AgencyNumberNameFacility] AS [AgencyNumberNameFacility]，
[Extent1] 。[CallDate] AS [CallDate]，
[Extent1]。[CallLength] AS [CallLength]，
[Extent1]。[Comments] AS [Comments]，
[Extent1]。[ StaffID] AS [StaffID]，
[Extent4]。[LastName] AS [LastName]
FROM [dbo]。[AdviceLineCall] AS [Extent1]
INNER JOIN [dbo]。[Agency ] AS [Extent2] ON [Extent1]。[AgencyNumber] = [Extent2]。[AgencyNumb呃]
INNER JOIN [dbo]。[StatusOfAdviceLineCaller] AS [Extent3] ON [Extent1]。[StatusOfAdviceLineCallerID] = [Extent3]。[StatusOfAdviceLineCallerID]
INNER JOIN [dbo]。[Staff] AS [ Extent4] ON [Extent1]。[StaffID] = [Extent4]。[StaffID]
LEFT OUTER JOIN [dbo]。[AdviceLineCallSubjectMatter] AS [Extent5] ON [Extent1]。[AdviceLineCallSubjectMatterID] = [Extent5]。[ AdviceLineCallSubjectMatterID]
WHERE 1 = [Extent4]。[Employed]
我不确定导致翻译差异的原因。两者都是使用EF 6.2的.NET 4.6。非常感谢任何建议！
我找到了问题的原因以及为什么要翻译相同的C＃代码以不同的方式进入SQL，这一切都与必需的数据注释有关。
表，AdviceLineCallSubjectMatter是数据库的一个新增功能。所以只有新的AdviceLineCall记录才会有一个 AdviceLineCallSubjectMatterID所以我把它变成了一个可以为空的int。
某些 AdviceLineCall字段是"必需的"，并且新的int？ AdviceLineCallSubjectMatterID被添加到我的 AdviceLineCall Model类中必需的数据注释。
公共部分类AdviceLineCall
{
。
。
。
[必需（ErrorMessage ="主题事项是必需的））
[DisplayName（" Subject Matter"）]
public int？AdviceLineCallSubjectMatterID {get; set ;}
。
。
。
}
公共部分类AdviceLineCallSubjectMatter
{
public AdviceLineCallSubjectMatter（）
{
AdviceLineCalls = new HashSet< AdviceLineCall> （）;
}
[DisplayName（" Subject Matter"）]
public int AdviceLineCallSubjectMatterID {get;组; }
[StringLength（3）]
[DisplayName（" Subject Matter"）]
public string AdviceLineCallSubjectMatterDesc {get;组; }
public virtual ICollection< AdviceLineCall> AdviceLineCalls {get;组; }
}
}
当我注释掉 在AdviceLineCall Model类中需要的数据注释，我的C＃被转换为期望的SQL，并且在AdviceLineCallSubjectMatter上具有LEFT OUTER JOIN。
我不确定为什么必需的数据注释具有这种效果？ ??
注意：在我创建的临时项目中测试相同的查询，我 通过Code First From DB创建了DB Context和Model类，并且没有必需的数据注释。
I've been using Linq to SQL and have started using Entity Framework for a new project. I use Linq Pad to test my code before incorporating it into Visual Studio. While debugging in VS, I noticed my Counts differed. When I inspected the SQL created by my code in VS, I noticed that it didn't translate correctly.
My code in VS:
var adviceLineCallTotalViewModelList = from a in db.AdviceLineCalls
join ag in db.Agencies on a.AgencyNumber equals ag.AgencyNumber
join st in db.StatusOfAdviceLineCallers on a.StatusOfAdviceLineCallerID equals st.StatusOfAdviceLineCallerID
join s in db.Staffs on a.StaffID equals s.StaffID
join sm in db.AdviceLineCallSubjectMatters on a.AdviceLineCallSubjectMatterID equals sm.AdviceLineCallSubjectMatterID into grp
from sm_left in grp.DefaultIfEmpty()
where s.Employed == true
select new AdviceLineCallTotalViewModel()
{
AdviceLineCallID = a.AdviceLineCallID,
AdviceLineCallSubjectMatterID = sm_left.AdviceLineCallSubjectMatterID,
AdviceLineCallSubjectMatterDesc = sm_left.AdviceLineCallSubjectMatterDesc,
StatusOfAdviceLineCallerID = st.StatusOfAdviceLineCallerID,
StatusOfAdviceLineCallerDesc = st.StatusOfAdviceLineCallerDesc,
AgencyNumber = a.AgencyNumber,
AgencyNumberNameFacility = ag.AgencyNumberNameFacility,
CallDate = a.CallDate,
CallLength = a.CallLength,
Comments = a.Comments,
StaffID = a.StaffID,
LastName = s.LastName
};
When I debug and look at the SQL generated, I see:
SELECT [Extent1].[AdviceLineCallID] AS [AdviceLineCallID], [Extent5].[AdviceLineCallSubjectMatterID] AS [AdviceLineCallSubjectMatterID], [Extent5].[AdviceLineCallSubjectMatterDesc] AS [AdviceLineCallSubjectMatterDesc], [Extent3].[StatusOfAdviceLineCallerID] AS [StatusOfAdviceLineCallerID], [Extent3].[StatusOfAdviceLineCallerDesc] AS [StatusOfAdviceLineCallerDesc], [Extent1].[AgencyNumber] AS [AgencyNumber], [Extent2].[AgencyNumberNameFacility] AS [AgencyNumberNameFacility], [Extent1].[CallDate] AS [CallDate], [Extent1].[CallLength] AS [CallLength], [Extent1].[Comments] AS [Comments], [Extent1].[StaffID] AS [StaffID], [Extent4].[LastName] AS [LastName] FROM [dbo].[AdviceLineCall] AS [Extent1] INNER JOIN [dbo].[Agency] AS [Extent2] ON [Extent1].[AgencyNumber] = [Extent2].[AgencyNumber] INNER JOIN [dbo].[StatusOfAdviceLineCaller] AS [Extent3] ON [Extent1].[StatusOfAdviceLineCallerID] = [Extent3].[StatusOfAdviceLineCallerID] INNER JOIN [dbo].[Staff] AS [Extent4] ON [Extent1].[StaffID] = [Extent4].[StaffID] INNER JOIN [dbo].[AdviceLineCallSubjectMatter] AS [Extent5] ON [Extent1].[AdviceLineCallSubjectMatterID] = [Extent5].[AdviceLineCallSubjectMatterID] WHERE 1 = [Extent4].[Employed]
The last "INNER JOIN" should be a "LEFT OUTER JOIN" because of the lines:
join sm in db.AdviceLineCallSubjectMatters on a.AdviceLineCallSubjectMatterID equals sm.AdviceLineCallSubjectMatterID into grp from sm_left in grp.DefaultIfEmpty()
I created another Visual Studio project, added the ADO.NET Data Provider, Code First from the DB, and copied and pasted the same code, and it works correctly. My C# code is correctly translated into SQL. The last Join is correctly translated into a LEFT OUTER JOIN:
SELECT
[Extent1].[AdviceLineCallID] AS [AdviceLineCallID],
[Extent5].[AdviceLineCallSubjectMatterID] AS [AdviceLineCallSubjectMatterID],
[Extent5].[AdviceLineCallSubjectMatterDesc] AS [AdviceLineCallSubjectMatterDesc],
[Extent3].[StatusOfAdviceLineCallerID] AS [StatusOfAdviceLineCallerID],
[Extent3].[StatusOfAdviceLineCallerDesc] AS [StatusOfAdviceLineCallerDesc],
[Extent1].[AgencyNumber] AS [AgencyNumber],
[Extent2].[AgencyNumberNameFacility] AS [AgencyNumberNameFacility],
[Extent1].[CallDate] AS [CallDate],
[Extent1].[CallLength] AS [CallLength],
[Extent1].[Comments] AS [Comments],
[Extent1].[StaffID] AS [StaffID],
[Extent4].[LastName] AS [LastName]
FROM [dbo].[AdviceLineCall] AS [Extent1]
INNER JOIN [dbo].[Agency] AS [Extent2] ON [Extent1].[AgencyNumber] = [Extent2].[AgencyNumber]
INNER JOIN [dbo].[StatusOfAdviceLineCaller] AS [Extent3] ON [Extent1].[StatusOfAdviceLineCallerID] = [Extent3].[StatusOfAdviceLineCallerID]
INNER JOIN [dbo].[Staff] AS [Extent4] ON [Extent1].[StaffID] = [Extent4].[StaffID]
LEFT OUTER JOIN [dbo].[AdviceLineCallSubjectMatter] AS [Extent5] ON [Extent1].[AdviceLineCallSubjectMatterID] = [Extent5].[AdviceLineCallSubjectMatterID]
WHERE 1 = [Extent4].[Employed]I am not sure what is causing the differences in translation. Both are .NET 4.6 using EF 6.2. Any advice would be greatly appreciated!
I found the cause of my problem and why the same C# code was being translated into SQL differently, and it all had to do with the Required Data Annotation.
The table, AdviceLineCallSubjectMatter is a new addition to the DB. So only new AdviceLineCall records will have a AdviceLineCallSubjectMatterID so I made it a nullable int.
There are certain AdviceLineCall fields that are "Required," and the new int? AdviceLineCallSubjectMatterID was added to my AdviceLineCall Model class with the Required data annotation.
public partial class AdviceLineCall { . . . [Required(ErrorMessage = "Subject Matter is required")] [DisplayName("Subject Matter")] public int? AdviceLineCallSubjectMatterID { get; set; } . . . } public partial class AdviceLineCallSubjectMatter { public AdviceLineCallSubjectMatter() { AdviceLineCalls = new HashSet<AdviceLineCall>(); } [DisplayName("Subject Matter")] public int AdviceLineCallSubjectMatterID { get; set; } [StringLength(3)] [DisplayName("Subject Matter")] public string AdviceLineCallSubjectMatterDesc { get; set; } public virtual ICollection<AdviceLineCall> AdviceLineCalls { get; set; } } }
When I comment out the Required data annotation in the AdviceLineCall Model class, my C# is translated to the expected SQL with a LEFT OUTER JOIN on AdviceLineCallSubjectMatter.
I am not sure why the Required data annotation has this effect???
NOTE: In the temporary project I created to test the same query, I created the DB Context and Model classes via Code First From DB and didn't have the Required data annotations.
这篇关于实体框架未正确地将C＃转换为SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
