LINQ - 100 Views - Same Columns - Generic Queries

Question

Hello,

Here is my scenario: We have a legacy system that has about 100 views that all pull the same columns worth of data.

Now, in my DataContext I have all the views in the context and I have a seperate query from each one. Each query's results loads into a single List that gets returned to the application.

Is it possible to have a single query that I can pass in a object to know which table to pull from?

Example:

var query = from GenericTable.Where(whereClause).Select(ObjectMap);

Note: i know this is not the right syntax, it is just for example only.

My main goal is to avoid having to write 100 different queries when they are all the same thing, just pointed to a different view each time.

Any suggestions are welcome, even if it is to keep the 100 queries.

Thanks!

Answer 1

One approach may be using Dynamic Linq. Then you can specify the where clause and the select using strings.

var genericList = GetView("predicate to identify view");
genericList.Where("field1 = @1", value1).select("Field1, Field2");

Answer 2

Create a common interface for all of the mapping types that were generated by linq to sql. Then use partial class definitions to add the interface to all the classes.

Then write your 100 queries like this:

public IQueryable<T> GetQueryAgainst<T>(IQueryable<T> source,
  string search) where T : IMyData
{
  IQueryable<T> result = source.Where(t => t.Name.Contains(search));
  return result;
}

and call it like:

List<Car> cars = GetQueryAgainst(dc.Cars, "Bob").ToList();
List<People> people = GetQueryAgainst(dc.People, "Bob").ToList();
List<Orders> orders = GetQueryAgainst(dc.Orders, "Bob").ToList();