GVKun编程网logo

EntityFramework LINQToEntities生成怪异的慢TSQL在哪里

14

这篇文章主要围绕EntityFrameworkLINQToEntities生成怪异的慢TSQL在哪里展开，旨在为您提供一份详细的参考资料。我们将全面介绍EntityFrameworkLINQToEnt

这篇文章主要围绕EntityFramework LINQToEntities生成怪异的慢TSQL在哪里展开，旨在为您提供一份详细的参考资料。我们将全面介绍EntityFramework LINQToEntities生成怪异的慢TSQL在哪里，同时也会为您带来.net – Linq到Sql和Entity Framework的未来、ASP.NET Core&EntityFramework核心：左(外)加入Linq、asp.net-mvc – EntityFramework – 连接字符串在哪里？、c# – Microsoft.AspNet.Identity.EntityFramework.IdentityUser的外键？的实用方法。

本文目录一览：

EntityFramework LINQToEntities生成怪异的慢TSQL在哪里

EntityFramework LINQToEntities生成怪异的慢TSQL在哪里

我需要明白这一点。在TSQL代码生成中，EF5.0和EF6。*之间存在很大差异。

在我的代码中，这是我的LINQ-statemant

var qry2 = context.viw_overview_1.Where(i => i.article_EAN17 == ean).Select(i => i.article_id).Take(200);

EntityFramework 5.0 仅生成一个简单而快速的TSQL WHERE-这样的语句，非常完美

... WHERE [Extent1].[article_EAN17] = @p__linq__000.0960096ms in SSMS

但是 EntityFramework 6. *会 生成一个非常复杂且缓慢的语句

... WHERE (([Extent1].[article_EAN17] = @p__linq__0) AND ( NOT ([Extent1].[article_EAN17] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[article_EAN17] IS NULL) AND (@p__linq__0 IS NULL))45.3665362ms in SSMS

字段article_EAN17也具有索引。但是EF6。仍然需要花很多时间才能初始化，但是 有没有一种方法
可以在EF6。中生成带有属性或类似属性的简单WHERE语句？我尝试了 string.Equals（） ， string.Compare （）
， 交换 了参数，但没有任何改变。

答案1

小编典典

我相信这与您在Entity Framework中的NULL比较设置有关

在查询之前添加以下代码，以查看它是否对查询性能有所帮助：

context.ContextOptions.UseCSharpNullComparisonBehavior = true;

.net – Linq到Sql和Entity Framework的未来

.net – Linq到Sql和Entity Framework的未来

我在一个项目上工作,想要使用ORM.我应该使用什么： Linq to sql或Entity Framework？正如我所看到的,Linq to sql提供了更多机会,例如对枚举类型的支持.然而,也有人说,Linq to sql是为了Linq的游戏和学习而微软的未来战略在于实体框架.那么我应该使用哪一个？

解决方法

据我所知,对LINQ to sql的支持不会随处可见,但正在进行开发的大部分资源都将用于LINQ to Entities.我认为无论哪个方向看起来都是更好的选择,你都是安全的.如果它是LINQ to sql,那么只要它拥有你需要的一切就可以了 – 不要指望新功能.

ASP.NET Core&EntityFramework核心：左(外)加入Linq

ASP.NET Core&EntityFramework核心：左(外)加入Linq

我正在尝试使用ASP.NET Core和EntityFramework Core在Linq中使用左连接.

两个表的简单情况：

人(id,名字,姓氏)

PersonDetails(id,PersonId,DetailText)

我尝试查询的数据是Person.id,Person.firstname,Person.lastname和PersonDetails.DetailText.
有些人没有DetailText,所以想要的结果是NULL.

在sql中它工作正常

SELECT p.id,p.Firstname,p.Lastname,d.DetailText FROM Person p 
LEFT JOIN PersonDetails d on d.id = p.Id 
ORDER BY p.id ASC

结果如预期：

# | id | firstname | lastname | detailtext
1 | 1  | First1    | Last1    | details1
2 | 2  | First2    | Last2    | details2
3 | 3  | First3    | Last3    | NULL

在我的Web API控制器中我查询：

[HttpGet]
public IActionResult Get()
{
    var result = from person in _dbContext.Person
                    join detail in _dbContext.PersonDetails on person.Id equals detail.PersonId
                    select new
                    {
                        id = person.Id,firstname = person.Firstname,lastname = person.Lastname,detailText = detail.DetailText
                    };
   return Ok(result);
}

招摇的结果是缺少人3(没有详细文本的人)

[
  {
    "id": 1,"firstname": "First1","lastname": "Last1","detailText": "details1"
  },{
    "id": 2,"firstname": "First2","lastname": "Last2","detailText": "details2"
  }
]

我在Linq做错了什么？

更新1：

感谢您的答案和链接到目前为止.

我使用into和.DefaultIfEmpty()复制并粘贴下面的代码,经过一些进一步的阅读后,我明白这应该有效.

不幸的是,事实并非如此.

首先,代码开始抛出异常,但仍然返回前两个结果(缺少NULL).从输出窗口复制粘贴：

System.NullReferenceException: Object reference not set to an instance of an object.
   at lambda_method(Closure,TransparentIdentifier`2 )
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer,IEnumerable values,JsonArrayContract contract,JsonProperty member,JsonContainerContract collectionContract,JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter,Object value,Type objectType)
   at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter,Type objectType)
   at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.WriteObject(TextWriter writer,Object value)
   at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.d__9.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__32.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__31.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__29.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__23.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__18.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.d__4.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.ApplicationInsights.AspNetCore.ExceptionTrackingMiddleware.d__4.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.ApplicationInsights.AspNetCore.RequestTrackingMiddleware.d__4.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Server.IISIntegration.IISMiddleware.d__8.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Hosting.Internal.RequestServicesContainerMiddleware.d__3.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Server.Kestrel.Internal.Http.Frame`1.d__2.MoveNext()
Microsoft.AspNetCore.Server.Kestrel:Error: Connection id "0HKVGPV90QGE0": An unhandled exception was thrown by the application.

System.NullReferenceException: Object reference not set to an instance of an object.
   at lambda_method(Closure,Object value)
   at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.d__9.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__32.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__31.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__29.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__23.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__18.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.d__4.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.ApplicationInsights.AspNetCore.ExceptionTrackingMiddleware.d__4.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.ApplicationInsights.AspNetCore.RequestTrackingMiddleware.d__4.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Server.IISIntegration.IISMiddleware.d__8.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Hosting.Internal.RequestServicesContainerMiddleware.d__3.MoveNext()
--- End of stack trace from prevIoUs location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Server.Kestrel.Internal.Http.Frame`1.d__2.MoveNext()

谷歌给了我一个：“LEFT OUTER JOIN PROBLEMS #4002”
以及“Left outer join @ Stackoverflow”

现在我不确定这是否仍然存在或应该已经修复的错误.我正在使用EntityFramework Core RC2.

解决方案1：导航属性

正如Gert Arnold在评论中指出的那样：使用导航属性

这意味着(工作)查询看起来像

var result = from person in _dbContext.Person
             select new
             {
                id = person.Id,detailText = person.PersonDetails.Select(d => d.DetailText).SingleOrDefault()
            };

return Ok(result);

在我的PersonExampleDB中,我没有正确设置外键,因此属性PersonDetails不在scaffolded模型类中.但是使用它是最简单的解决方案(并且工作甚至可以快速工作)而不是现在的连接(参见错误报告).

当加入方式有效时,仍然对更新感到满意.

解决方法

如果你需要执行Left连接,那么你必须使用into和DefaultIfEmpty(),如下所示. 
var result = from person in _dbContext.Person
             join detail in _dbContext.PersonDetails on person.Id equals detail.PersonId into Details
             from m in Details.DefaultIfEmpty()
               select new
                {
                    id = person.Id,detailText = m.DetailText
                };

您可以了解更多信息：Left Outer Join in LINQ to Entities

asp.net-mvc – EntityFramework – 连接字符串在哪里？

asp.net-mvc – EntityFramework – 连接字符串在哪里？

我从我的web.config中删除了连接字符串，而实体框架仍然连接到数据库！连接字符串在哪里设置？这是一个问题，因为我需要使我的网站的实时版本指向实时数据库。

解决方法

这是一个有趣的事实，当我们尝试连接到现有的数据库(像你在做)时，我发现了“配置惯例”的概念。

如果您的DbContext类(例如northwind)位于命名空间(例如MvcProject)中，则由于某种原因，EF将与名为“northwind”(或“MvcProject.northwind”)的web.config中的连接字符串的类名称不匹配，然后它将创建一个默认为本地sqlEXPRESS实例的连接字符串，数据库名为“MvcProject.northwind”。这将是一个空数据库。而且，您会意识到您没有连接到正确的数据库，您的头脑会弄清楚为什么您不会收到任何数据。

我遇到这种情况(不是优雅的，但它是我找到解决它的最快的方法)：添加一个构造函数到您的DbContext类，调用基础与web.config中连接字符串的名称 – 例如。

namespace MvcProject
{
    public class northwind : DbContext
    {
        public northwind() : base("northwind") {}
    }
}

希望有帮助的人在那里;-)

c# – Microsoft.AspNet.Identity.EntityFramework.IdentityUser的外键？

c# – Microsoft.AspNet.Identity.EntityFramework.IdentityUser的外键？

我在VS 2013,刚刚创建了一个MVC应用程序.

我正在创建一个对象,我打算在结果数据库中有一个AspNetUsers表的外键.该项目确实有一个ApplicationUser(派生自IdentityUser),看起来像是与AspNetUsers表的属性列匹配.

我们如何正确地为此声明外键？

public MyObject
{
   public string UserId { get; set; }

   [ForeignKey("UserId")]
   public ApplicationUser User { get; set;}

   // other properties
}

现在,我修改ApplicationUser以获得MyObjects的集合：

public ApplicationUser : IdentityUser
{
     public virtual ICollection<MyObject> MyObjects { get; set; }    
}

这似乎是如何在EF Code First中进行一对多操作.但是,当我更新数据库时,我收到的错误表明身份成员(IdentityUserLogin,IdentityUserRole等)没有定义键.也许那些课程不打算参加EF Code First Migrations？

我可以“到后面”并通过sql语句添加外键,但如果我想从Code First再次更新,我可能会收到错误(数据库当前不匹配旧的迁移或类似的东西).

我们如何正确地使用外键参考这些会员表？

我还尝试使用AspNetUsers表的匹配属性创建一个AspNetUser类.我没有在Client类上使用“public ApplicationUser”,而是声明了“public AspNetUser”.这样做会导致迁移失败 – “未应用自动迁移,因为它会导致数据丢失”.

那么该怎么办？

解决方法

在ApplicationUser和MyObject之间创建一对多关系很容易,并在MyObjects表中添加“UserId”外键.我喜欢这个解决方案的是它遵循EF约定,并且模型中不需要[ForeignKey]属性： 
public class ApplicationUser : IdentityUser
{
    public virtual ICollection<MyObject> MyObjects { get; set; }
}

public class MyObject
{
    public int MyObjectId { get; set; }

    public string MyObjectName { get; set; }

    // other properties

    public virtual ApplicationUser ApplicationUser { get; set; }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection",throwIfV1Schema: false)
    {
    }

    public DbSet<MyObject> MyObjects { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<MyObject>()
            .Hasrequired(c => c.ApplicationUser)
            .WithMany(t => t.MyObjects)
            .Map(m => m.MapKey("UserId"));
    }
}

请注意使用Fluent API在MyObjects表中创建“UserId”外键.这个解决方案仍然可以在不添加Fluent API的情况下工作,但是按照惯例,您的外键列将在MyObjects表中命名为“ApplicationUser_Id”.

今天关于EntityFramework LINQToEntities生成怪异的慢TSQL在哪里的介绍到此结束，谢谢您的阅读，有关.net – Linq到Sql和Entity Framework的未来、ASP.NET Core&EntityFramework核心：左(外)加入Linq、asp.net-mvc – EntityFramework – 连接字符串在哪里？、c# – Microsoft.AspNet.Identity.EntityFramework.IdentityUser的外键？等更多相关知识的信息可以在本站进行查询。

本文标签：

上一篇如何在不使用FETCH的情况下访问游标列.. INTO

下一篇MySQL SELECT INTO是否等效？

相关文章