CODEWITHSUNDEEP
.netasp.net-mvcentity-framework-4
eaglestorm
eaglestorm

Reputation: 1202

entity framework 4 generic list method with filters

I have started using the entity framework for a web application and would like to know what the best way would be to allow users to filter lists dynamically. i.e. If we have a list of people the user can filter by lastname, city, etc.

The problem I am having is that I am using EF 4 with code first and all the fitlering I can find is using Linq queries but I can't see a way to build up the where clause for the filter dymaically based on the filter options the user has selected. i.e. in SQL you could build,

select * from people, address where lastname = 'jones' and address.city = 'sydney'

Is there a way to build up this list dynamically using linq?

EDIT
The solution I'm going to try will be similar to this Implementing Dynamic Searching Using LINQ. As I prefer to be as generic as possible where I can.

Upvotes: 1

Views: 1066

Answers (3)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

The way to do this is for example defining some type for search criteria:

public class PeopleSearchCriteria
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string City { get; set; }
}

and define custom extension method for IQueryable<Person> :

public static IQueryable<Person> FilterBySearchCriteria(this IQueryable<Person> query, 
     PeoplseSearchCritera criteria)
{
    if (!String.IsNullOrEmpty(criteria.FirstName))
    {
        string firstName = criteria.FirstName;
        query = query.Where(p => p.FirstName == firstName);
    }

    // do similar code for other criterias

    return query;
}

Now you only need to create model binder (or use default one if possible) to fill your serach options to PeopleSearchCriteria instance and execute simply:

var data = context.People.FilterBySearchCriteria(searchCriteria).ToList();

If you really want some dynamic approach you can build expression tree manually or check Dynamic Linq (you will lose compile time checks).

Upvotes: 3

tugberk
tugberk

Reputation: 58444

I am suggesting to use repository pattern for such kind of things

http://msdn.microsoft.com/en-us/library/ff649690.aspx

here is an example;

public class PeopleRepository { 

  HumanEntities _entities = new HumanEntities();

  public IQueryable<people> GetAll() {

    IQueryable<people> query = _entities.Customers;

    retun query;

  }

  public IQueryable<people> GetAll(string _lastname, string _city) {

    //I am thinking that people and address tables are related to each other 
    //as one to many or many to many. So the example should look like below;
    var query = GetAll().Where(x => x.lastname = _lastname && x.address.city = _city); 

    retun query;

  }

  public void Save() { 

    _entities.SaveChages()

  }
}

and after that you can use them outside of your class easily. like below;

PeopleRepository _repo = new PeopleRepository();

DataList1.DataSource =  _repo.GetAll("ugurlu", "LA");

you mentioned that you want the parameters as user input. I do not know where you will use your entity model (asp.net web forms, win forms or asp.net mvc), but here is an example of that;

PeopleRepository _repo = new PeopleRepository();

DataList1.DataSource =  _repo.GetAll(LastnameTextBox.Text, CityTextBox.Text);

Upvotes: 0

Damb
Damb

Reputation: 14600

For example:
This gets you filtered collection of people.

var people = EfDBContextEntities.people; // depends on your context and naming

var filteredPeople = 
from p in people
where p.lastname == "jones"
select p;

If you want to return both entities in one collection, than you can do something like: 

var myCollection = 
from p in people
from a in address
where p.lastname == "jones"
where a.city == "sydney"
select new {person = p, address = a};

You will get collection of objects and you will be able to access them like: 

foreach (var item in myCollection)
{
  var personName = item.person.lastname;
  var cityAddress = item.address.city;
}

Upvotes: 0

Related Questions