LINQ to Entities does not recognize 'System.DateTime AddMinutes(Double)' method

Question

Runtime Error that says what the title shows... My Linq is as follows in my controller:

     var TestingLinq = (from a in db.shp_master
                         join b in db.shp_status on a.serialnbr equals b.serialnbr into b_join
                         from b in b_join.DefaultIfEmpty()
                         where
                                   a.shipto == "1022a" &&
                                   a.status == "s" &&
                                   b.status == "s" &&
                                   a.shpreq == 0
                         group new {a, b} by new {
                                   a.serialnbr,
                                   a.trailer,
                                   a.shipto
                         } into g
                         orderby
                                   g.Key.serialnbr
                         select new RecieveTruck {
                                   SerialNumber = g.Key.serialnbr,
                                   TrailerNumber = (g.Key.trailer ?? "N/A"),
                                   Shipped = g.Min(p => p.b.datetimestamp),
                                   ETA = null,
                                   ShipTo = g.Key.shipto == "1026" ? (System.DateTime?)Convert.ToDateTime(g.Min(p => p.b.datetimestamp)).AddMinutes(180) :
                                   g.Key.shipto == "2020" ? (System.DateTime?)Convert.ToDateTime(g.Min(p => p.b.datetimestamp)).AddMinutes(195) :
                                   g.Key.shipto == "2017" ? (System.DateTime?)Convert.ToDateTime(g.Min(p => p.b.datetimestamp)).AddMinutes(180) : 
                                   g.Key.shipto == "nor" ? (System.DateTime?)Convert.ToDateTime(g.Min(p => p.b.datetimestamp)).AddMinutes(180) : null
                         });
return View(TestingLinq);

And then in my view I have the following:

@model IEnumerable<TestingMainPage.Models.RecieveTruck>
@if (Model.Count() > 1 )
        {
            foreach (var tl in Model)
            {              
            <tr>
                <td align="center" valign="middle" nowrap="nowrap" class="bodyTextTL"><img src="~/Content/images/view.gif" alt="View Load" style="cursor:pointer;" onclick="document.location.href = '/RecieveTruck/ViewLoad?LoadID=' + @tl.SerialNumber + ''" /></td>
                <td align="center" valign="middle" nowrap="nowrap" class="bodyTextTL">@tl.SerialNumber</td>
                <td align="center" valign="middle" nowrap="nowrap" class="bodyTextTL">@tl.TrailerNumber</td>
                <td align="center" valign="middle" nowrap="nowrap" class="bodyTextTL">@tl.Shipped</td>
                <td align="center" valign="middle" nowrap="nowrap" class="bodyTextTL">@tl.ETA</td>
                <td align="center" valign="middle" nowrap="nowrap" class="bodyTextTLR">
                    <input type="button" name="Receive Load" value="Receive Load" style="cursor: pointer;" class="bodyText6" onmouseover="this.style.color='orangered'; this.style.fontWeight='bold';" onmouseout="    this.style.color='black'; this.style.fontWeight='normal';" onclick="document.location.href = '/RecieveTruck/Execute'" />&nbsp
                </td>
            </tr>
            }
        }

So my question is, how to I go about passing that through to my view so I can use it to populate the tables I am creating on my web-program.. I understand why it would not give me a error before running it, but how am I suppose to pass this to my view?

http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions(v=vs.110).aspx — ta.speot.is, Jul 15 '14 at 12:30
Do you understand what the error means and why you're getting it? — Tim Rogers, Jul 15 '14 at 12:30
Try using the [SqlFunctions](http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.aspx) methods. The `AddMinutes` cannot be translated to SQL by the provider. — user247702, Jul 15 '14 at 12:30
And for those who use EF, there is also [EntityFunctions](http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.addminutes(v=vs.110).aspx) — deherch, Jul 15 '14 at 12:31
@Yuliam Chandra, scartag already created answer below. I assume there is enough information here to solve Kyle Rickaby's problem :) — deherch, Jul 15 '14 at 12:35

score 2 · Answer 1 · answered Jul 15 '14 at 12:39

There is no need to run that logic on the database itself - you can run it on the application server after you've selected the necessary data.

So change your query to just select the raw shipTo value. Then call AsEnumerable() which will pop you back into standard LINQ-to-objects, and calculate your ShipTo value there.

TestingLinq = (from 
...
select new {
                               SerialNumber = g.Key.serialnbr,
                               TrailerNumber = (g.Key.trailer ?? "N/A"),
                               Shipped = g.Min(p => p.b.datetimestamp),
                               ETA = null,
                               Timestamp = p.b.datetimestamp,
                               ShipTo = g.Key.shipto
})
  .AsEnumerable()
  .Select(x => new ReceiveTruck { 
          SerialNumber = x.SerialNumber,
          ...
          ShipTo = CalculateShipTo(x.Timestamp, x.ShipTo)
  })

I've omitted the implementation of CalculateShipTo but you should get the idea.

Is my, running that logic on the database the reason for the error? Or would this just be an alternate route? Also, p is not in that context, and neither is b for your Timestamp, so even if I add that to the module (which I am assuming is just to use the calculateShipTo) will give me another error... This is like my second week with linq and this is a super complex (for me) query so I guess I dont get it... — Kyle Rickaby, Jul 17 '14 at 13:17
Sorry it took so long to reply, ended up on another project in the middle of this one... — Kyle Rickaby, Jul 17 '14 at 13:23

score 1 · Answer 2 · answered Jul 15 '14 at 12:32

1

You can use EntityFunctions instead. it's in System.Data.Entity.Core.Objects

EntityFunctions.AddDays(DateTime, num);

answered Jul 15 '14 at 12:32

scartag

17,548
3
48
52

I am assuming you mean like this? – Kyle Rickaby Jul 17 '14 at 13:19
ShipTo = g.Key.shipto == "1026" ? EntityFunctions.AddMinutes(DateTime, 180) : because when I do that it tells me that... 'System.Data.Entity.Core.Objects.EntityFunctions' is obselete and has been replaced by System.Data.Entity.DbFunctions which I cannot add, also it tells me that DateTime is a 'type' but is used like a variable – Kyle Rickaby Jul 17 '14 at 13:22
Sorry it took so long to reply, ended up on another project in the middle of this one... – Kyle Rickaby Jul 17 '14 at 13:25
1

ShipTo = g.Key.shipto == "1026" ? System.Data.Entity.DbFunctions.AddMinutes("DATETIMEHERE", 180) : – Kyle Rickaby Jul 17 '14 at 13:28

LINQ to Entities does not recognize 'System.DateTime AddMinutes(Double)' method

2 Answers2