Same query with many (MySql-)Tables

JensDoe

Learning LINQ (and LinqPad), I have come across the following problem: while querying a MySql-DB I need to evaluate the same query against many identical (huge) tables (except for the data ;-)).
My query is build like this one:

var query =
from s in table_2000
where s.Name.Contains("John")
select new { s.Name, s.Age };

With tables from 1970 to 2013. The MySql-Connection is properly set up and working. Now I need a way to put my tables into some kind of List to use them in a foreach loop:

foreach(var tableYear in SomeIEnumerable)
{
var query =
from s in tableYear
where s.Name.Contains("John")
select new { s.Name, s.Age };
...
}

Can someone point me in the right direction?

Thanks!

JoeAlbahari

foreach(var tableYear in SomeIEnumerable)
{
  var query =
  from s in GetTable<table_2000>(tableYear)
  ...

JensDoe

Somehow I managed to get my explanation not clear enough.

How does your code manage to access all the other tables (table_2001, table_2002, etc.)?

foreach(var tableYear in SomeIEnumerable) { var query = from s in GetTable<table_2000>(tableYear) ...

If tableYear is for example 'table_2001' I get
var query = from s in GetTable<table_2000>("table_2001") ...

and s is still 'LINQPad.User.table_2000' and not 'LINQPad.User.table_2000'.

My problem was rather how to get all Tables and put them in 'SomeIEnumerable' to have something like this:
foreach(var table in SomeIEnumerableWithAllTables) { var query = from s in table ...

Thanks for your support! :-)

PS: How to get your (better looking) code style? I used code-Tags. Doesn't look that good.

JoeAlbahari

It shouldn't matter that type parameter is table_2000 for every query, because the schema is the same for all tables. The type parameter just indicates what type to map the table to. You can pick any table name because the schemas are all identical.

JensDoe

Well, I might start again. Maybe I've damaged something with the driver-setup.
My 's' is always 'LINQPad.User.table_2000' - no matter, what parameter I give GetTable(). It "seems" to work, because I get an "InvalidOperationException: AttributMapping: the member 'table_2014' does not exist on type 'TypedDataContext' error, if I give a non existing table name as parameter.

Thanks again. :-)

JensDoe

[Solved]
I had to extend the line you wrote from

from s in GetTable<table_2000>(tableYear) to

from s in GetTable<table_2000>(tableYear).ProviderTable


Thanks again!

rgutmann

Hi,

I get an error "No overload for method 'GetTable' tables 1 arguments"

I'd appreciate any advice for how to fix it.

Thanks very much!