Dynamically loading SQL tables in the Entity Framework

Question

I need to dynamically access some SQL tables, hoping using Entity Framework. Here are a few pseudo codes:

var Account = DB.Accounts.SingleOrDefault(x => x.ID == 12345);

which will return an Account object to me, and it contains some fields called "PREFIX", "CAMPAIGN ID", and additional information about accounts is stored in separate SQL tables with the naming convention PREFIX_CAMPAIGNID_MAIN.

All tables have the same fields, so I thought about creating a new object that is not displayed anywhere, and then dynamically loads it, for example:

var STA01_MAIN = new MyAccount(); // my "un-mapped" entity

DB.LoadTable('STA01_MAIN').LoadInto(STA01_MAIN);

Now I can get something about the STA01_MAIN account: STA01_MAIN.AccountId .

So my question is: how do I access these tables using the Entity Framework?

Answer 1

I do not think EF has a LoadTable and LoadInto method, but ObjectOntext.ExecuteStoreQuery may be what you are looking for:

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

This should allow you to make an arbitrary query on your database, and then match the results with the arbitrary type you specified (even if it was not mapped otherwise in EF).

It goes without saying that you are responsible for compiling a query that provided the necessary columns for matching to the destination type, as well as for adjusting the specified query when changing this type.

Here are some further discussions regarding its use.

http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/44cf5582-63f8-4f81-8029-7b43469c028d/

Have you considered mapping all of these tables (with the same columns) to inheritance relationships in EF, and then querying them as

db.BaseTypes.OfType<SpecificType>().Where(/*.....*/);