LINQ的EF7犯规与联接和日期的工作？ [英] Linq on EF7 doesnt work with Joins and Dates?

问题描述

我有我的ASP.NET MVC6 EF7 web应用程序运行从控制器查询一些麻烦...

I am having some troubles running a query from a Controller on my ASP.NET MVC6 EF7 web app...

示范和的DbContext在这个previous问： EF7配置错误中的DbContext？

The Model and DbContext are in this previous ask: EF7 Incorrect configuration of the DBContext?

当我尝试运行下面的Linq查询包含联接的一对夫妇，并试图从一个数据库某些条目特定日期出现问题...

The problem appears when I try to run the following Linq query which contains a couple of joins and tries to get some entries from a Database for a particular date...

public IActionResult GetEntries(int year, int month, int day)
{
    //_context.Database.SetCommandTimeout(180);
    string dateTest = new DateTime(year, month, day).ToString("yyyy-MM-dd");

    var results = (from c in _context.Comments
                   join r in _context.Reviews on c.ReviewId equals r.ReviewId
                   join f in _context.Films on c.ReviewId equals f.ReviewId 
                   where c.Author.Equals("AuthorTest")
                   && (c.Created.CompareTo(new DateTime(year, month, day, 0, 0, 0)) >= 0) && (c.Created.CompareTo(new DateTime(year, month, day, 23, 59, 59)) < 0)
                   && !r.Status.Contains("Enabled")
                   select new 
                   {
                       ReviewId = c.ReviewId,
                       ReviewStatus = r.Status,
                       Author = c.Author
                   });
    var results2 = results.ToList();
    return View(results2);
}

这是我得到的例外是...

The Exception that I get is...

类型的'System.Data.SqlClient.SqlException'例外发生在
  EntityFramework.Core.dll而不是在用户code处理

An exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.Core.dll but was not handled in user code

附加信息：超时过期。超时时间已过
  之前的操作或服务器完成没有响应。

Additional information: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

有趣的是....如果我没有运行下面的行完全相同的查询，那么它完美

The funny thing is.... if I run the EXACT same query without the following line, then it works perfectly

&& (c.Created.CompareTo(new DateTime(year, month, day, 0, 0, 0)) >= 0) && (c.Created.CompareTo(new DateTime(year, month, day, 23, 59, 59)) < 0)

所以...超时异常的事情不作太多的感觉对我来说，因为一旦我删除和状况，它返回条目万人。

So... the timeout exception thing doesn't make too much sense to me because once I remove the AND condition, it returns thousand of entries.

另外，如果我塞了一个ASP.NET MVC5 EF6相同的查询，查询就像使用AND条件的魅力...

Also, if I plug that same query on a ASP.NET MVC5 EF6, the query works like a charm with the AND condition...

我是什么在这里失踪？

最后，我想另一件事是建立一个单一的Linq查询，没有加入，与日期条件和它的作品也很好...

Finally, another thing that I tried was to create a single Linq query with no join and with the date condition and it works also perfectly...

public IActionResult GetEntries(int year, int month, int day)
{
    //_context.Database.SetCommandTimeout(180);
    string dateTest = new DateTime(year, month, day).ToString("yyyy-MM-dd");

    var results = (from c in _context.Comments
                   where c.Author.Equals("AuthorTest")
                   && (c.Created.CompareTo(new DateTime(year, month, day, 0, 0, 0)) >= 0) && (c.Created.CompareTo(new DateTime(year, month, day, 23, 59, 59)) < 0)
                   select new 
                   {
                       ReviewId = c.ReviewId,
                       Author = c.Author
                   });
    var results2 = results.ToList();
    return View(results2);
}

任何指针？

谢谢！

推荐答案

我想这是因为你的LINQ到实体提供者不知道如何翻译的CompareTo 方法为SQL。尝试创建要查询的比较之外，后来的日期尝试将它们比较查询，因为我显示如下：

I think this is because your Linq to Entities provider doesn't know how to translate the CompareTo method to SQL. Try creating the dates you want to compare outside of your query and later try to compare them in your query as I show below:

var d1=new DateTime(year, month, day, 0, 0, 0);
var d2=new DateTime(year, month, day, 23, 59, 59);
var results = (from c in _context.Comments
               join r in _context.Reviews on c.ReviewId equals r.ReviewId
               join f in _context.Films on c.ReviewId equals f.ReviewId 
               where c.Author.Equals("AuthorTest")
               && c.Created >= d1 && c.Created<d2)
               && !r.Status.Contains("Enabled")
               select new 
               {
                   ReviewId = c.ReviewId,
                   ReviewStatus = r.Status,
                   Author = c.Author
               });

这篇关于LINQ的EF7犯规与联接和日期的工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！