Trying to create some dynamic linq

Question

Hi Folks,

I'm trying to create a linq query based on some dynamic/optional arguments passed into a method.

User [Table] -> zero to many -> Vehicles [Table]
User [Table] -> zero to many -> Pets

So we want all users (including any vechile and/or pet info). Optional filters are

• Vehicle numberplate
• Pet name

Because the vehicle and pet tables are zero-to-many, i usually have outer joins between the user table and the vehicle|pet table.

To speed up the query, i was trying to create the dynamic linq and if we have an optional argument provided, redfine the outer join to an inner join.

(The context diagram will have the two tables linked as an outer join by default.)

Can this be done?

I'm also not sure if this SO post can help me, either.

Answer 1

If you are trying to change tables or joins of a LINQ to SQL query at runtime you need to do that with reflection. LINQ expressions are not special; same as working with any other object call - you can change the value of properties and variables at runtime, but choosing which properties to change or which methods to call requires reflecting.

I would add to that by pointing out dynamically creating LINQ expressions via reflection is probably a little silly for most (all?) cases, since under the hood the expression is essentially reflected back into SQL statements. Might as well write the SQL yourself if you are doing it on-the-fly. The point of LINQ is to abstract the data source from the developer, not the end-user.

Answer 2

I think you are heading in the wrong direction. You can easily use the fact that LINQ queries are composable here.

First, you would always use the outer join, and get all users with the appropriate vehicles and pets:

// Get all the users.
IQueryable<User> users = dbContext.Users;

Then you would add the filters if necessary:

// If a filter on the pet name is required, filter.
if (!string.IsNullOrEmpty(petNameFilter))
{
   // Filter on pet name.
   users = users.Where(u => u.Pets.Where(
     p => p.Name == petNameFilter).Any());
}

// Add a filter on the license plate number.
if (!string.IsNullOrEmpty(licensePlateFilter))
{
  // Filter on the license plate.
  users = users.Where(
    u => u.Cars.Where(c => c.LicensePlace == licensePlateFilter).Any());
}

Note that this will not filter out the pets or cars that don't meet the filter, as it is simply looking for the users that have pets with that name, or cars with that plate.

Answer 3

This is how I do what you are asking...

var results = u from dc.Users
join veh from dc.vehicles on u.userId equals v.userId into vtemp from v in vtemp.DefaultIfEmpty()
join pet from dc.pets on u.userId equals p.userId into ptemp from p in ptemp.DefaultItEmpty()
select new { user = u, vehicle = v, pet = p };

if ( !string.IsNullOrEmpty(petName) )
{
 results = results.Where(r => r.pet.PetName == petName);
}
if ( !string.IsNullOrEmpty(licNum) )
{
 results = results.Where(r => r.vehicle.LicNum == licNum);
}