Entity Framework Linq to Entities and variable entity name

Question

Hi all,

using Entity Framework and LINQ to Entities,
how to use variable entity name?


example:

in C#, and table is Articles

 webDBEntities webDB = new webDBEntities();
 var article = webDB.Articles
    .FirstOrDefault(a => a.Title == pageName);

trying to localize the contents, I created a table for each language and mapped as follows:
Articles_en
Articles_ar
Articles_es
etc..

I want to be able to pass a language variable to query the proper table (entity):
 

 // for simplicity, lang is given a value manually
 string lang = 'en' ;
 string tblName = "Articles_" + lang;
 webDBEntities webDB = new webDBEntities();
 var article = webDB.tblName
    .FirstOrDefault(a => a.Title == pageName);

I have searched and found
http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/af89b44f-8c8d-45b8-94c0-a3ff69528ccf

using getType and datacontext mapping, but did not really know how to work it out, it and this is my first project using LINQ as well as EF.

I tried

 

 

   string tblName = "Articles_"+user_lang;
   Type tmpEntity = Type.GetType("CompanyName.Models." + tblName);

but then what?! i could not query the table fields after playing with that code.

so back to square one, and trying to search for a way to use variable table name.

Any and all help will be greatly appreciated!

Thanks,

Hussain

Answer 1

You can map all of the language tables individually and use GetTable or dynamic linq, but that means there will be one entity class for each table. A less messy option would be to merge all the tables into one single table with a language column, and simply filter on the language column. That is cleaner both db-side and in your code.

---

 

Cool tools for Linq-to-SQL and Entity Framework 4:  huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)  huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4

Answer 2

Thanks for your time reading the post and for your reply.

First, seems i was confused about LINQ to entities , and LINQ to SQL. After some readings, what is available is the ObjectContext, not the DataContext ( i tried dc.GetTable() and did not work).

(maybe should edit the title of this thread to avoid any confusion).

It is not a problem regarding the individual mapping. In reality, the edmx will be refreshed (if needed).I  appreciate your suggestion and have actually thought of doing it but decided to go with separate table approach ...(one reason, a language could be dropped without side effects).

 

currently, after reading a book about EF 4 and spending a whole week searching I reached this :

 

string tblName = "CompanyName.Models.Articles_es";
var article = webDB.Articles_en.First();  // fetch object to load metadata<br/>
var assembly = Assembly.GetAssembly(article.GetType());

//var articleByLang = assembly.CreateInstance("CompanyName.Models.Articles_ar");
var articleByLang = assembly.CreateInstance(tblName);

this gives me the right entity type, but I could not access its properties nor could i make a query on it.

 

also i tried this approach:

 

var articleEntity = webDB.CreateQuery<EntityObject>(tblName);
// use the context to inspect metadataWorkspace
var _c = articleEntity.Context;
MetadataWorkspace workspace = _c.MetadataWorkspace;
// [container]->[Articles], [Articles_ar], etc..
var sets = from container in workspace.GetItems<EntityContainer>(DataSpace.CSpace)
from set in container.BaseEntitySets
where set is EntitySet
select set;

string output="";
foreach (EntitySet set in sets)
{
// 
output.="{0} holds instance of {1}", set.Name, set.ElementType.FullName);
}

 

it gets the list of entities in current container

next step should be returning one entity that has the proper name,

so the application continues seamlessly as if there was no language checking happened.

 

a third (earlier approach) was

 

string sSQL = "select * from "+tblName+" where title='"+pageName+"'";<br/>
var art = webDB.ExecuteStoreCommand(sSQL);

 

and this worked for displaying the data..however, when i need to edit/delete a record.. it means i will lose the beauty of using EF and automatic CRUD functionality, and will have to change the whole EF L2E work and start ado.net DAL...

 

I have read about dynamic linq too, but did not any way that a table name or entitySet is passed at runtime.

 

if you have an example (or workaround) of using getTable with ObjectContext to achieve deciding which table to use, or using linq dynamic, it will be of great help.

 

thanks

Hussain

 

Answer 3

Look into EF's TPC inheritance - that should allow you to base all of them on a base class while having separate tables and sub-entities for each language table.

See http://blogs.microsoft.co.il/blogs/gilf/archive/2010/01/25/table-per-concrete-type-inheritance-in-entity-framework.aspx for a sample

 

---

 

Cool tools for Linq-to-SQL and Entity Framework 4:  huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)  huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4

Answer 4

Thanks again for your follow up Kristofer, and the time searching for a solution.

 

I have considered mapping multiple tables to one class, entity split, and inheritance (such as the one explained in above example), 

but the problem at the end is:

deciding which Entity to work with based on a string value .

while trying to visualize the ObjectContext, i tried this:

var myEntities  = {

     baseEntitySet = { {articles_en}, {articles_ar}, {articles_es}, etc..}

}

if each object inside baseEntitySet has a name, and can be compared to a string, then i can get the EntitySet.

 

 

var
 articleEntity = webDB.CreateQuery<EntityObject>(tblName);
// use the context to inspect metadataWorkspace

var
 _c = articleEntity.Context;
MetadataWorkspace workspace = _c.MetadataWorkspace;
// [container]->[Articles], [Articles_ar], etc..

var
 sets = from
 container in
 workspace.GetItems<EntityContainer>(DataSpace.CSpace)
from
 set
 in
 container.BaseEntitySets
where
 set
 is
 EntitySet
select
 set
;

string
 output=""
;
foreach
 (EntitySet set
 in
 sets)
{ 

 // check name against a string value

 if (set.Name.Equals("Articles"))

 {

  return set;

 }

}

trying:

http://localhost:4227/en/Page/Mission

it gives an error:

 

 

The model item passed into the dictionary is of type 'System.Data.Metadata.Edm.EntitySet', but this dictionary requires a model item of type 'CompanyWebsite.Models.Article_en'.

 

for fetching data,

the solution relies on/in the ability to walk an object and return a member based on a string. i am pretty sure it's very easy but just can't figure out how to do it in Entity Framework or LINQ to entities.

 

for inserting data,

it's the assembly way in my last reply, then i guess attache the object and add new object.

 

for now, i will rely on building sql strings till i find a solution.

 

thanks again.

 

 

Answer 5

Any update on this ?

I am trying to do the same but can't figure out how...

Thanks