Going from the mapping domain back to the DataContext domain

Question

Hi there:

Suppose I have a DataContext with several entity tables, suppose I am given a tableName and a key,

I want to get the entity table to be able to perform a linq query like EntityTable.Where(d => d.Key == Key), but without having to code a switch statement like:

              switch (tableName)
              {
                 case "Customers":
                    return db.Customers.Where(c => c.key == key).Select(c => c).FirstOrDefault();
                    break;

                 case...
              }

But, something like this instead:

              return GetEntityTable(tableName).Where(c => c.key == key).Select(c => c).FirstOrDefault();

Note: I suppose I have to use the Mapping information, but once I get the mapped table, I don't know how to get back to the DataContext domain to perform the query.

Thanks in advance,

Rafael

---

Believe you can do it, and you will!!

Answer 1

Hi Rafael,

Could you try to use entity sql to query?

using (ObjectContext oc = new ObjectContext(myConnectionString, "NorthwindContainer")) {

    oc.Connection.Open();

    string myQuery = "SELECT VALUE c.CategoryName FROM Categories as c";

    foreach (string s in new ObjectQuery<string>(myQuery, oc)) {

        Console.WriteLine(s);

    }

}

There are more samples in this blog,
http://blogs.msdn.com/adonet/archive/2007/05/30/entitysql.aspx

Does this work for you?


Best Regards
Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Thanks Yichun_Feng for answering:

What I need is to be able to iterate through the entity tables of a DataContext, and not iterating through the values of a specific column.

I learned to iterate through the members of an entity table using the linq mapping namespace, but now I need to do the same with the entity tables. I'm not quite sure that it can be done...

Rafael

---

Believe you can do it, and you will!!

Answer 3

Hi rafsots,

Since the query is string, you can build any type you want. For example,

"SELECT VALUE c.CategoryName FROM " + tablename + " as c"

Does this works for you?


Best Regards
Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

But, something like this instead:

              return GetEntityTable(tableName).Where(c => c.key == key).Select(c => c).FirstOrDefault();

Note: I suppose I have to use the Mapping information, but once I get the mapped table, I don't know how to get back to the DataContext domain to perform the query.

Two steps:

1) To get hold of a ITable/IQueryable for a table by table name, you can use the datacontext's mapping member; it lets you explore all entities and how they are mapped. An extension method like this:

public static Type GetEntityTypeFromTableName(this DataContext dc, string qualifiedTableName)
{
    MetaTable table = dc.Mapping.GetTables().Where(t => t.TableName.Equals(qualifiedTableName, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault();
    if (table != null)
    {
        return table.RowType.Type;
    }
    else
    {
        throw new ArgumentException("Invalid table name.");
    }
}

...will allow you to do sometihng like this:

IQueryable empQuery = dc.GetTable(dc.GetEntityTypeFromTableName("HumanResources.Employee"));

...note that "qualifiedTableName may need to use brackets around the schema and/or table name; reserved keywords, table names with special characters etc are bracketed by L2S...



2) Since the above give us a ITable/IQueryable of unknown generic type, we can't go loose on it with lambdas without casting it to its' generic type. We can however pass it to the dynamic linq library (System.Linq.Dynamic) and append the where clause, projections, sorting etc using the extension methods in System.Linq.Dynamic... Check out http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

---

Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)

Answer 5

Hi KristoferA:

Thanks for answering, it worked great!

---

Believe you can do it, and you will!!

Answer 6

Hi KristoferA:

It worked, the problem is that one can't get a complete entity out of Dinamic Linq, just a projection, like "new(Name, Address...)", the existing documentation of Dinamic Linq doesn't say anything about obtaining a whole entity.

However, this Dinamic Linq could be handy when sending queries over a network, since it's a limitation of Linq.

If you know a way of getting a whole entity, not just a projection, I appreciate you send a message...

Rafael.

---

Believe you can do it, and you will!!

Answer 7

It worked, the problem is that one can't get a complete entity out of Dinamic Linq, just a projection, like "new(Name, Address...)", the existing documentation of Dinamic Linq doesn't say anything about obtaining a whole entity.

Use .Select("it") to get the entity itself, instead of a projection...

Answer 8

Hello,

I need to implement this exact functionality because I have different pages but with same UI and functionality and different tables

I got the type by giving table name as a input but I dont know ahead also it is suggested but I cant do. some thing like

I want to use query like below query where Customer would be replaced with unknown table type.
public IQueryable<Customer> GetCustomerByID(
    int customerID)
{
    return
        from cust in Context.Customer
        where cust.CustomerID == customerID
        select cust;
}

 


I dont know How to get table properties and fields by casting it to type which one is passing to the method.

Answer 9

HI,

Yet my query is unsolved, Can anybody help me out in it??

Answer 10

Hello,

I need to implement this exact functionality because I have different pages but with same UI and functionality and different tables

I got the type by giving table name as a input but I dont know ahead also it is suggested but I cant do. some thing like

I want to use query like below query where Customer would be replaced with unknown table type.
public IQueryable<Customer> GetCustomerByID(
    int customerID)
{
    return
        from cust in Context.Customer
        where cust.CustomerID == customerID
        select cust;
}

 


I dont know How to get table properties and fields by casting it to type which one is passing to the method.

Take a look at System.Linq.Dynamic - it allows you to build linq queries from strings:
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 11

Hello ,

May be it would be very easy but I cant get it.

IQueryable

empQuery = this.Context.GetTable(GenericClass1.GetEntityTypeFromTableName(this.Context, "dbo.PT_AdjustmentTypeCodes"));

 

foreach (var item in empQuery)

{

 

}

Now here I want to convert item to dbo.PT_AdjustmentTypeCodes

is it possible??