Entity Framework - how to join tables without LINQ and with only string?

Question

Hi all,

I have a question about Entity Framework. Please answer if you know answer on this. I have such query :

String queryRaw =
    "SELECT " +
    "p.ProductName AS ProductName " +
    "FROM ProductEntities.Products AS p " +
    "INNER JOIN CategoryEntities.Categories AS c " + 
    "ON p.CategoryID = c.CategoryID ";

ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(queryRaw, entityContext);

GridView1.DataSource = query;
GridView1.DataBind();

Particularly I want to join few tables in one query, but I can NOT use LINQ and can NOT use ObjectQuery with objects mapped to DB fields inside my query. Because each entity creates dynamically. So this is what i can NOT use :

msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic12

msdn.microsoft.com/en-us/library/bb896339%28v=VS.90%29.aspx

The question is can I use something like this instead of using objects?

query.Join ("INNER JOIN CategoryEntities.Category ON p.CategoryID = c.CategoryID ");

The purpose is to use Join method of ObjectQuery with syntax as in Where method :

msdn.microsoft.com/en-us/library/bb338811%28v=VS.90%29.aspx

Thanks, Artem

Answer 1

Any decision i see right now is to temporary convert ObjectQuery to string, add joined table as string and then convert it back to ObjectQuery :

RoutesEntities routesModel = new RoutesEntities(entityConnection);
String queryRaw = "SELECT " + 
                  "rs.RouteID AS RouteID, " +
                  "rs.LocaleID AS LocaleID, " + 
                  "rs.IsSystem AS IsSystem " +
                  "FROM RoutesEntities.Routes AS rs ";

_queryData = new ObjectQuery<DbDataRecord>(queryRaw, routesModel);

var queryJoin = _queryData.CommandText + " INNER JOIN LocalesEntities.Locales AS ls ON ls.LocaleID = rs.LocaleID ";
_queryData = new ObjectQuery<DbDataRecord>(queryJoin, routesModel);

Maybe someone has more consistent suggestions?

Answer 2

Finally I Found a better solution for this, we can use Sub Query inside main Query. For example :

var db = CustomEntity();

ObjectQuery<Categories> query1 = db.Categories.Where("it.CategoryName='Demo'").Select ("it.CategoryID");
var categorySQL = query1.ToTraceString().Replace("dbo", "CustomEntity"); // E-SQL need this syntax
ObjectQuery<Products> query2 = db.Categories.Where("it.CategoryID = (" + categorySQL + ")");

Some example is here :

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

Good luck!