Call Class Method Inside Linq Request

Question

I have a GetAge (DateTime birthDay) method. I want to use this method in Linq Query, passing the birthday, and based on the returned age value, I need to perform some logic.

I need below LINQ query -

from customer in contetx.Customer where if GetAge(customer.BirthDate) > 20 and customer.accountType="Savings" or if(GetAge(customer.BirthDate) > 40 and customer.AccountType="Current" 

Immediate help would be greatly appreciated.

Answer 1

 context.Customer .AsEnumerable() // because the method has no translation to SQL .Where(customer => (GetAge(customer.BirthDate) > 20 && customer.AccountType == "Savings") || (GetAge(customer.BirthDate) > 40 && customer.AccountType == "Current")); 

.AsEnumerable is required if you are trying to query an SQL database because the GetAge method in your code will not be translated into SQL. In this case, calling .AsEnumerable returns the query results to this point, and you then work with local objects that your method can work on.

If you do not want to get all the results at this point, because the number of records is large, you can always replicate the logic from the method you want to call in your request (I assume that the logic is here)

 context.Customer.Select(c => new { Customer = c, Age = (DateTime.Today.Year - c.BirthDate.Year) } .Where(c => (c.Age > 20 && c.Customer.AccountType == "Savings") || (c.Age > 40 && c.Customer.AccountType == "Current")) .Select(c => c.Customer); 

Since all operations are available in SQL, this will work.

If the method you are trying to call is particularly complex, you can always port it to an extension method that accepts IQueryable and returns IQueryable . The contents of the method should still have a valid SQL translation, but this will help to hide more complex logic.

For example, the above query might look like this:

 context.Customers.WhoAreValidByAge(); 

Where WhoAreValidByAge is defined as:

 public static IQueryable<Customer> WhoAreValidByAge(this IQueryable<Customer> customers) { cusomters.Select(c => new { Customer = c, Age = (DateTime.Today.Year - c.BirthDate.Year) } .Where(c => (c.Age > 20 && c.Customer.AccountType == "Savings") || (c.Age > 40 && c.Customer.AccountType == "Current")) .Select(c => c.Customer) } 

If the logic contained in your method doesn’t translate into SQL for some reason, although you have no choice but to convert the results set to LinqToObjects. In this case, I suggest filtering the results as much as possible in SQL before calling AsEnumerable .

Answer 2

 var customers = from customer in contetx.Customer let age = GetAge(customer.BirthDate) where (age > 20 && customer.accountType == "Savings") || (age > 40 && customer.accountType == "Current") select customer; 

Answer 3

You can do it:

 var query = from customer in contetx.Customer where (GetAge(customer.BirthDate) > 20 && customer.AccountType == "Saving") || (GetAge(customer.BirthDate) > 40 && customer.AccountType == "Current") select customer; 

You can bypass the GetAge call twice using the let keyword, as Darin did.

 var query from customer in // ... let age = GetAge(customer.BirthDate) where // ... select customer;