Alternative to use a method in a query to the database in c# using entity framework

Question

i separated my application into a DAL, BL, UI.

I used entity framework code first throw repositories to access the sql database.

public class Person{
   ...
}    
public class PersonRepository{

   Create(...){...}
   Update(...){...}
   Delete(...){...}
   GetById(...){...}
   Query(...){...}

   ...

Now the thing is the BL i'm working on a method to get all the Persons who are leaving near an adress

 public GetPersonsNear(string Address){
 ...
 }
 private bool AddressesAreClose(string address1, string address2)
 {
 ...
 }

the thing is linq does'nt let me use my method (in a query passed in the "Query" method of the repository)

 ...
 PersonRepository personRepository = new PersonRepository();
 var person = repository.Query(p => AddressAreClose(adress,p.Adress); 
 ...

therefor i needed to get All the elements of the table in a list using a simple foreach loop to make the tests and keeping only the relevant ones

  ...
 PersonRepository personRepository = new PersonRepository();
 var persons = personRepository.GetAll;
 foreach(person in persons)
 {
    if(AdressAreClose(adress,person.adress))
    ...
  }

for now i populated the database with only a few elements to test it, but i'm not sure it would work very well with the far more greater number it will contain later specially with all the test i'm planing to add

isn't there a more clever way to do this ??? I'm open to anything

RPM1984 · Accepted Answer · 2011-07-10 23:43:59Z

Well first of all, you should use generics in your repository, even if it's constrained to Person. This way you can build pipes/filters off your queries to clean up your LINQ queries and facilitate reuse.

Of course, without seeing the full signature/implementation of your Query method, it's hard to tell. But either way, you need to return IEnumerable<Person> or IQueryable<Person> to make the following work.

So, you could turn AddressesAreClose into a pipe/filter, like this:

public static bool WhereAddressesAreClose(this IQueryable<Person> source, string address)
{
   return source.Where(/* your conditions */);
}

Then you can use it in your LINQ query:

var person = repository
   .Query() // Should be IQueryable<Person>
   .WhereAddressAreClose(adress);
   .ToList();

Depending on the size of your data and whether or not your implementing caching, you should limit the results on the server (database), not post-query with a foreach loop.

If the performance isn't great, consider adding indexes, using compiled queries or moving to a stored procedure.

Collectives™ on Stack Overflow

Alternative to use a method in a query to the database in c# using entity framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related