Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Call class method inside the Linq Query

Tags:

c#

linq

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

I want below query in LINQ format -

  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 highly appreciated.

like image 292
CHash_Mike Avatar asked Nov 03 '10 09:11

CHash_Mike


People also ask

What is any () in LINQ?

The Any operator is used to check whether any element in the sequence or collection satisfy the given condition. If one or more element satisfies the given condition, then it will return true. If any element does not satisfy the given condition, then it will return false.

How LINQ query execute?

LINQ queries are always executed when the query variable is iterated over, not when the query variable is created. This is called deferred execution. You can also force a query to execute immediately, which is useful for caching query results.

Can I use ToString in LINQ?

Net. LINQ to Entities does not recognize the method System. String ToString() method, and this method cannot be translated into a store expression. In following code snippet, a LINQ to SQL query is executed on the records returned from the Entity Framework.


2 Answers

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

The .AsEnumerable is required if you're attempting to query an SQL database as the GetAge method in your code will have no translation to SQL. In that case the call to .AsEnumerable retrieves the results of the query up to that point and you're then working with local objects on which your method can operate.

If you don't want to retrieve all the results at that point because the number of records is large you could always replicate the logic from the method you want to call in your query (I'm guessing at the logic 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);

Because the operations are all available in SQL this will work.

If the method you're trying to call is particularly complex you can always move it to an extension method that takes an IQueryable and returns an IQueryable. The contents of the method will still need to have a valid translation to SQL but it will help hide more complicated logic.

For example the above query could be made to 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 to SQL for some reason though you have no choice but to convert the results set to LinqToObjects. In that case I'd suggest filtering the results as much as possible in SQL before calling AsEnumerable.

like image 62
Dave D Avatar answered Nov 15 '22 23:11

Dave D


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;
like image 40
Darin Dimitrov Avatar answered Nov 15 '22 21:11

Darin Dimitrov