CODEWITHSUNDEEP
c#entity-frameworklinqinttype-conversion
weeraa
weeraa

Reputation: 1195

Convert to Int with LINQ

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."

Upvotes: 5

Views: 14845

Answers (3)

Liviu Boboia
Liviu Boboia

Reputation: 1754

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();

Upvotes: 1

Mikhail Tulubaev
Mikhail Tulubaev

Reputation: 4281

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

Upvotes: 1

grek40
grek40

Reputation: 13458

DbFunctions.DiffDays(tody, a.InvoiceDate) is returning a Nullable<int>, so instead of Convert.ToInt32 try one of the following

DbFunctions.DiffDays(tody, a.InvoiceDate).GetValueOrDefault();
DbFunctions.DiffDays(tody, a.InvoiceDate) ?? someIntegerDefaultValue;
DbFunctions.DiffDays(tody, a.InvoiceDate).Value;

The first one is for the case where you know the result can be null and you want to get a 0 instead for this case. The second is when you want a different default value or compute some more complex replacement and the third is for the case where you know that the result should never ever be null and you are ok to get an exception if it is null.

Upvotes: 0

Related Questions