LINQ with anonymous class/properties

Question

I am moving a project from working with standard DB queries to working with EF and LINQ. I have a table that has certain records that I would use to build a query that would look like the following:

select * from client where city = ?

In my original table, I would be pulling client and city from the table to build that query.

It is also possible that client and city above could be another table and/or field altogether. How would I do the same thing with EF and LINQ? Is this even possible or do I have to build a separate class to handle all of that logic?

var query = from c in context.clients
            where c.city == ?
            select c;

Edit: This isn't about joining queries. It's about building dynamic queries. I don't know when I run the program whether I will be querying on city, address, or any even on the "client" table itself. It could be on another table. I want to be able to dynamically build the queries.

Answer 1

If your tables are joined by foreign keys, you can access values in the other table using LINQ as follows. (I'm extending your example by assuming that there's an "addresses" table and the clients table has a foreign key to it.)

var query = from c in context.clients
            where c.address.city == ?
            select c;

Edit: (trying to understand your original question and followup comment...)

I think what you're asking is whether you can do something like this?

string cityName = "Los Angeles"; // Could be a parameter, etc.

var query = from c in context.clients
            where c.city == cityName
            select c;

Or, to take it a step further from your question, maybe something like this:

string cityName = (from c in context.cities
                   where c.id == 5
                   select c.name).FirstOrDefault();

var query = from c in context.clients
            where c.city == cityName
            select c;

Since it's an IQueryable, you can add further conditions as well, on the fly.

if (someCondition)
{
    query = from q in query
            where q.someField >= conditionValue
            select q;
}

Or whatever. The query expression tree won't actually be evaluated/executed until the results are actually needed.

Answer 2

you can write the query presented by GalacticCowboy like

var query = from c in context.clients
        join add in context.Addresses on c.AddressID equals add.AddressID
        where addr.city == ?
        select c;

both queries will generate inner join there is no difference in performance. The only difference is if Address is optional for clients then c.address.City will throw exception on client that does not have address whereas this query will return an empty enumeration

Answer 3

If you really want to build your queries at runtime, then there are a couple of linq-based solutions available - you can compile the code at runtime into a temporary assembly or you can use DynamicLinq from one of the MS examples.

For more info, see the questions and answers in: How to create LINQ Query from string?

Answer 4

I think there are a few approaches that you can take.

First, you could look into the Dynamic LINQ library (and also see David Fowler's update to it here). Using this approach, you can write your LINQ queries like this:

var results = Context.Clients
                .Where("city=='Los Angeles'")
                .OrderBy("address");

So your Where and OrderBy predicates are strings that get converted to Expressions under the hood.

Second, you could use a library like PredicateBuilder if you know what your querying on but not sure if you'll be querying on one or more fields, like so:

var predicate = PredicateBuilder.True<Clients>();
foreach(var criteria in searchCriteria) {
  if (criteria.Key=="city"){
    predicate = predicate.And(c => c.city==criteria.Value);
  } else if (criteria.Key=="address"){
    predicate = predicate.And(c => c.address==criteria.Value);
  }
}
var results = Context.Clients.Where(predicate);

Third, and probably the most difficult, is to build your own Expression Tree. This definitely requires the most code (and it's a little deep at first), but it's very, very powerful. This example from MSDN gives a good walk-through on what you can do (and probably does it a little more concisely than I could do here).

So, basically, you have a few options available to you. The Dynamic LINQ library seems to be the easiest to use, but I've never used it, so I can't say for sure how well it works and how reliable it is. There's also a NuGet package for the Dynamic LINQ Library, if that helps.

Good luck. Hope this helps!

Answer 5

If you want to build dynamic queries in Entity Framework, you CAN go through the trouble of trying to build the expression tree dynamically. However, the Entity Framework gives you some additional options.

First, EntityObject has some additional Object Services methods which already let you build a string and pass that in directly into a number of the predicates, including Where:

c.Customers.Where("City = 'London'");

If you need to build larger queries, including dynamically setting the query source, consider using EntitySQL. For example with EntitySQL, you can use simple string parsing and generate code like the following:

string entitySQL = "SELECT VALUE c FROM Customers AS c WHERE c.Address.City = 'Seattle';";
ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

You can see both of these options in action in the Entity Framework Query Samples.