Convert to Int with LINQ

Question

Below LINQ has a few Convert.ToInt32 methods. But it doesn't work. Referring to the internet, it is used int.Parse instead of convert. Still gives error. Please show me a direction to do this.

In below query tody's data type is DateTime

var ds = (from a in dbSetInvHeader
          join b in dbSetCustomer on a.BusinessEntityID equals b.Id
          join c in dbSetFinancialInfo on b.Id equals c.Id
          where (a.TotalAmount - a.AppliedAmount - a.ApplyToInvoiceCreditAmount) > 0
                      && DbFunctions.AddDays(a.InvoiceDate, Convert.ToInt32(c.CreditPeriod)) >= tody
          select new OverDueInvoices
                      {
                          CustomerName = b.Name,
                          InvoiceNo = a.InvoiceNo,
                          InvoiceAmount = a.TotalAmount - a.ApplyToInvoiceCreditAmount,
                          DueAmount = (a.TotalAmount - a.AppliedAmount - a.ApplyToInvoiceCreditAmount),
                          CreditAmount = a.ApplyToInvoiceCreditAmount,
                          NoOfDays = Convert.ToInt32(DbFunctions.DiffDays(tody, a.InvoiceDate))
                      }).ToList();

Update :

The code is using Entity Framework for above LINQ throws errors:

When using Convert.ToInt32:

"LINQ to Entities does not recognize the method 'Int32 ToInt32(System.Decimal)' method, and this method cannot be translated into a store expression."

When using int.Parse:

"LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression."

Solution 1

SQL does not know about the Convert.ToInt32 function. There doesn't seem to be a workaround to convert a string to an int in Entity Framework. What you can do is add another column to you object NoOfDaysString

public string NoOfDaysString {get; set;}
public string NoOfDays {get { return  Convert.ToInt32(NoOfDaysString); } ;}

and rewrite your query as such

var ds = (from a in dbSetInvHeader
                  join b in dbSetCustomer on a.BusinessEntityID equals b.Id
                  join c in dbSetFinancialInfo on b.Id equals c.Id
                  where (a.TotalAmount - a.AppliedAmount - a.ApplyToInvoiceCreditAmount) > 0
                  && DbFunctions.AddDays(a.InvoiceDate, Convert.ToInt32(c.CreditPeriod)) >= tody
                  select new OverDueInvoices
                  {
                      CustomerName = b.Name,
                      InvoiceNo = a.InvoiceNo,
                      InvoiceAmount = a.TotalAmount - a.ApplyToInvoiceCreditAmount,
                      DueAmount = (a.TotalAmount - a.AppliedAmount - a.ApplyToInvoiceCreditAmount),
                      CreditAmount = a.ApplyToInvoiceCreditAmount,
                      NoOfDaysString = Convert.ToInt32(DbFunctions.DiffDays(tody, a.InvoiceDate))

                  }).ToList();

This is the only workaround i can think of for the where statement. Add the CreditPeriod in your object and do the where after the objects are in the memory

var ds = (from a in dbSetInvHeader
              join b in dbSetCustomer on a.BusinessEntityID equals b.Id
              join c in dbSetFinancialInfo on b.Id equals c.Id
              where (a.TotalAmount - a.AppliedAmount - a.ApplyToInvoiceCreditAmount) > 0
              select new OverDueInvoices
              {
                  CustomerName = b.Name,
                  InvoiceNo = a.InvoiceNo,
                  InvoiceAmount = a.TotalAmount - a.ApplyToInvoiceCreditAmount,
                  DueAmount = (a.TotalAmount - a.AppliedAmount - a.ApplyToInvoiceCreditAmount),
                  CreditAmount = a.ApplyToInvoiceCreditAmount,
                  NoOfDaysString = Convert.ToInt32(DbFunctions.DiffDays(tody, a.InvoiceDate))
                  CreditPeriod = c.CreditPeriod
              })ToList().Where(t=>Convert.ToInt32(t.CreditPeriod) >= NoOfDays).ToList();

Solution 2

As @LiviuBoboia noted, SQL does not know anything about both Convert.ToInt32 and int.Parse methods and EF could not correctly convert call to this method to sql. Instead of this you can do the simple cast:

NoOfDaysString = (int)DbFunctions.DiffDays(tody, a.InvoiceDate)

this cast will be converted to sql as sql function CONVERT and this should work good.

Although, DbFunctions.DiffDays returning a Nullable<int> or int? so it would be better to avoid casting it to (int) as you can get InvalidCastException while trying to convert null to int