The EF (LINQ) framework uses a small tip in the process to summarize dbfunctions

This blog summarizes some of the problems I have encountered in the actual project about EF or LINQ, as notes for later review or for Yimeimei reference (updates when problems are encountered).

Directory

• The use of the 1:dbfunctions.truncatetime () technique
• Tips for using datetime fields in 2:linq and Dbfunctions.createdatetime () 2016/4/2 "new"

The use of the 1:dbfunctions.truncatetime () technique

Have you ever encountered a mistake like this:

• LINQ to Entities does isn't recognize the method ' System.String toshortdatestring () ' method, and this method cannot is trans Lated into a store expression. "The LINQ to entities does not recognize the ' System.String toshortdatestring () ' Method"
• System.NotSupportedException:The specified type member ' Date ' is not supported in LINQ to entities. Only initializers, entity members, and entity navigation properties is supported. "LINQ to Entities does not support type member ' Date ', only the initializer is supported, Entity members and entity navigation properties

 
var query = from order in _orderRepository.GetAll()
                .Where(o => o.OrderType == OrderType.LineSold)
                .WhereIf(input.OrderDate!=DateTime.MinValue,o=>o.OrderDate.Date==input.OrderDate.Date)
                .WhereIf(!string.IsNullOrEmpty(input.OrderNo),o=>o.OrderNo==input.OrderNo||o.OrderNo.EndsWith(input.OrderNo))
                .WhereIf(input.Status!=-1,o=>o.Status==input.Status) join device in _deviceRepository.GetAll()
            .WhereIf(!string.IsNullOrEmpty(input.Code),d=>d.Code==input.Code) on order.TerminalID equals device.Id join trans in _transDetailRepository.GetAll()
            .WhereIf(!string.IsNullOrEmpty(input.PayOrderNo),t=>t.PayOrderNo==input.PayOrderNo||t.PayOrderNo.EndsWith(input.PayOrderNo))
            on order.OrderNo equals trans.OrderNo into leftJoinResults from leftJoinResult in leftJoinResults.DefaultIfEmpty( ) select new LineSoldOrderOutput
    {
        Code = device.Code,
        Id = order.Id,
        Amount = order.PayFee,
        OrderDate = order.OrderDate,
        OrderNo = order.OrderNo,
        PayOrderNo = leftJoinResult.PayOrderNo??"还没产生支付方订单号",
        Status = order.Status
    };

Why is there such a demand?
Because the datetime types in the database are all2016-03-11 11:25:59saved in this form, and I am querying the data by the client, as long as the incoming date is on the line, it does not need to pass in the time part, so the time part must be clicked off (cut off).

As in the third line of the code above, it was written at the beginning, and the result reported the second error above.
The second time, the.WhereIf(input.OrderDate!=DateTime.MinValue,o=>o.OrderDate.ToShortDateString()==input.OrderDate.ToShortDateString())result reported the first mistake above.

Solutions

.WhereIf(input.OrderDate!=DateTime.MinValue,o=>DbFunctions.TruncateTime(o.OrderDate)"I'm using ef6,truncate, which translates as truncation , and the function, as its name implies, is to remove the time part, leaving only the date part"
EF6 you might need to use it before.EntityFunctions.TruncateTime(p.date) == dateWithoutTime

Other highlights
The code above also has a connection to three tables using LINQ, and more importantly, the first two tables are internal and then left connected. Not familiar with LINQ syntax can be learned.

Tips for using datetime fields in 2:linq and Dbfunctions.createdatetime ()

Sometimes, when working with data, you need to group the data, and the DateTime type fields are grouped by year and month, using the LINQ method syntax and the query syntax, respectively:

Method syntax

dateIncomeDtos = query
    .Where(o => o.OrderDate >= input.Start && o.OrderDate < DbFunctions.AddMonths(input.End,1))
    .OrderBy(o => o.OrderDate)
    .GroupBy(o => DbFunctions.CreateDateTime(o.OrderDate.Year, o.OrderDate.Month, 1, 0, 0, 0))
    .Select(group => new DateIncomeDto { Date = group.Key.Value, Income = group.Sum(item => item.PayFee ?? 0) });

query syntax

 
dateIncomeDtos = from q in query
    group q by new {date = new DateTime(q.OrderDate.Year, q.OrderDate.Month, 1)}
    into g
    select new DateIncomeDto
    {
        Date = g.Key.date
    };



  

	
   



  

The method syntax uses the Createdatetime method in the Dbfunctions class provided in LINQ, passing an integer from 1 to 29 for the day parameter, guaranteeing that it will be available each month (I pass in 1), so that, when grouped, EF groups The year and month of the OrderDate field for each record in the database.
The idea of query syntax is the same, except for anonymous classes.

The EF (LINQ) framework uses a small tip in the process to summarize dbfunctions