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

• • Reply
  

  artemiusgrea...

  Member

  11 Points

  29 Posts

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

  Sep 30, 2010 03:58 PM|LINK

  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 :
  
  http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic12
  http://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 :
  
  http://msdn.microsoft.com/en-us/library/bb338811%28v=VS.90%29.aspx
  
  Thanks, Artem

  SQL ObjectQuery Entity-Framework

• • Reply
  

  artemiusgrea...

  Member

  11 Points

  29 Posts

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

  Oct 01, 2010 12:00 AM|LINK

  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?

  • Marked as answer by artemiusgreat on Oct 03, 2010 06:53 PM
• • Reply
  

  artemiusgrea...

  Member

  11 Points

  29 Posts

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

  Oct 07, 2010 09:38 PM|LINK

  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!

  • Marked as answer by artemiusgreat on Oct 07, 2010 09:38 PM