CODEWITHSUNDEEP
c#linq
AKA
AKA

Reputation: 27

LinQ query - Cannot use Date as its non-primitive EDM type. Also cannot convert DateTime to String

I have below linQ Query for which I am getting the error:
LINQ to Entities does not recognize the method 'System.String ToString(System.DateTime)' method, and this method cannot be translated into a store expression.

var query =   from i in Expenditure
              join d in Department on i.DepartId equals d.dID
              where i.Status == "Audit"
              group i by new { i.InvoiceId, d.Title, i.InvoiceDate } into g
              select new
              {
                id = g.Key.InvoiceId,
                txt = g.Key.Title + " // " + g.Key.InvoiceId + " // " + Convert.ToString(g.Key.InvoiceDate)
              };

I want txt output as follow:

Health // Inv001 // 12-03-2018
Education // Inv002 // 23-03-3018

Problem is with InvoiceDate (Datetime) field for which I initially wrote (without the Convert)as: g.Key.InvoiceDate
but I got error as: Unable to cast the type 'System.DateTime' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

Can anyone suggest an easy solution? Thanks.

Upvotes: 0

Views: 218

Answers (2)

tchelidze
tchelidze

Reputation: 8318

Instead of assembling txt in database and force Linq To Entities to transform your logic into SQL, first retrieve the data and then apply transformations

Try following

var query =   
         (from i in Expenditure
          join d in Department on i.DepartId equals d.dID
          where i.Status == "Audit"
          group i by new { i.InvoiceId, d.Title, i.InvoiceDate } into g
          select new
          {
            id = g.Key.InvoiceId,
            title = g.Key.Title,
            invoiceId = g.Key.InvoiceId,
            invoiceDate =  g.Key.InvoiceDate
          })
          .ToList()
          .Select(it => new 
            { 
               id = it.id, 
               txt = it.title + " // " + it.invoiceId + " // " + it.invoiceDate 
            });

Upvotes: 1

Danny Varod
Danny Varod

Reputation: 18068

ToString isn't an SQL function, therefore it can't be done within the SQL query.

Try this:

var query =   (from i in Expenditure
              join d in Department on i.DepartId equals d.dID
              where i.Status == "Audit"
              group i by new { i.InvoiceId, d.Title, i.InvoiceDate } into g
              select new
              {
                g.Key.InvoiceId,
                g.Key.Title,
                g.Key.InvoiceDate,
              }).ToList()
              .select new
              {
                id = InvoiceId,
                txt = Title + " // " + InvoiceId + " // " + Convert.ToString(InvoiceDate)
              };

Everything after the .ToList() will be done in memory, where you can use any .NET method.

Upvotes: 1

Related Questions