SQL generated by LINQ for logging

Question

Hi all.

Plz help. I have this query and its result implements IEnumerable.

RoutesEntities routesModel = new RoutesEntities();
LocalesEntities localesModel = new LocalesEntities();

var routesQuery = from rs in routesModel.Routes.ToList()
                  join ls in localesModel.Locales.ToList() 
                  on rs.LocaleID equals ls.LocaleID
                  select new
                  {
                      LocaleID = rs.LocaleID,
                      RouteName = rs.RouteName
                  };

Question : How to get generated SQL from this query, or how to convert its type to ObjectQuery to use routesQuery.ToTraceString(). Is it possible?

Reason : I want to log all SQL queries to DB, though I don't want to create new object for joined contexts (tables)

Important : I can't use ObjectQuery initially because after all I use

ListView.DataSource = routesQuery;
ListView.DataBind() 

And it can cause an error of using to different contexts (DB tables) for one datasource.

So what should I do get generated SQL query?

Thanks in advance.

Answer 1

You go against two distinct data context so you'll run different queries on each and join the result in the client. The queries in the database can be logged as Femaref already pointed out, for LINQ-to-SQL contexts. EF ObjectContext still has work to do to catch up with LINQ-to-SQL and does not offer logging. However the ObjectQuery.CommandText does return the query text. The join in the client cannot be logged since ... it doesn't exist. It is is not SQL query, is a LINQ-to-Objects loop.

Answer 2

var routesQuery = from rs in routesModel.Routes.ToList() 
                  join ls in localesModel.Locales.ToList()  

The way you have this query set up, you are loading the entire Routes table into memory and also loading the entire Locales table into memory. Your queries (there are two), both look like:

select fieldlist
from tablename

You should separate the two queries into separate ObjectQuery instances, and log them individually. But more than that, you should probably not read the unfiltered contents of a table into memory!

Answer 3

There is the answer :

http://arteinvolo.org.ua/2010/09/21/how-to-use-entity-framework-with-multiple-entities/

public static EntityConnection setSchema(string[] edmxFiles) 
{
            XNamespace edmxns = "http://schemas.microsoft.com/ado/2007/06/edmx";
            XNamespace edmns = "http://schemas.microsoft.com/ado/2006/04/edm";
            XmlDocument edmxXml = new XmlDocument();
            XmlNamespaceManager nameSpace = new XmlNamespaceManager(edmxXml.NameTable);

            List<XmlReader> ssdlReader = new List<XmlReader>();
            List<XmlReader> csdlReader = new List<XmlReader>();
            List<XmlReader> mslReader = new List<XmlReader>();

            foreach (string edmxFile in edmxFiles)
            {
                edmxXml.Load(edmxFile);
                nameSpace.AddNamespace("edmx", edmxns.NamespaceName);

                XmlNode ssdlNode = edmxXml.SelectSingleNode("//edmx:StorageModels", nameSpace);
                XmlNode csdlNode = edmxXml.SelectSingleNode("//edmx:ConceptualModels", nameSpace);
                XmlNode mslNode = edmxXml.SelectSingleNode("//edmx:Mappings", nameSpace);

                ssdlReader.Add(XmlReader.Create(new StringReader(ssdlNode.InnerXml)));
                csdlReader.Add(XmlReader.Create(new StringReader(csdlNode.InnerXml)));
                mslReader.Add(XmlReader.Create(new StringReader(mslNode.InnerXml)));
            }

            StoreItemCollection storageCollection = new StoreItemCollection(ssdlReader);
            EdmItemCollection edmCollection = new EdmItemCollection(csdlReader);
            StorageMappingItemCollection mappingCollection = new StorageMappingItemCollection(edmCollection, storageCollection, mslReader);

            MetadataWorkspace workSpace = new MetadataWorkspace();
            workSpace.RegisterItemCollection(storageCollection);
            workSpace.RegisterItemCollection(edmCollection);
            workSpace.RegisterItemCollection(mappingCollection);

            SqlConnection metaConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomSqlConnection"].ToString());
            EntityConnection entityConnect = new EntityConnection(workSpace, metaConnect);

            return entityConnect;
}

How to use :

EntityConnection entityConnection = Connector.setSchema(new string[] {
                Path.GetFullPath(AppDomain.CurrentDomain.BaseDirectory + "/App_Model/RoutesModel.edmx"),
                Path.GetFullPath(AppDomain.CurrentDomain.BaseDirectory + "/App_Model/LocalesModel.edmx")
});

entityConnection.Open();
String queryData = "SELECT rs.RouteID, rs.RouteURL, ls.LocaleName, ls.IsActive, ls.LocaleDescription FROM RoutesEntities.Routes AS rs INNER JOIN LocalesEntities.Locales AS ls ON ls.LocaleID = rs.LocaleID";
EntityCommand queryCommand = new EntityCommand(queryData, entityConnection);
DbDataReader routesReader = queryCommand.ExecuteReader(CommandBehavior.SequentialAccess);

ListView.DataSource = routesReader;
ListView.DataBind();