arash wrote:
Hi,
I have a question regarding the sql statement generated by LLBLGenPro when using its Linq provider.
I'm using the latest build of application:
SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll: 2.6.8.1211
SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll: 2.6.8.1219
I have built an example against Northwind database in SQL Server 2005.
When I run the following query:
var q = linqMetaData.Products.OrderBy(p => p.ProductName).Select(product => new { ProductName = product.ProductName })
the generated sql is:
SELECT [LPLA_1].[ProductName] FROM [Northwind].[dbo].[Products] [LPLA_1] ORDER BY [LPLA_1].[ProductName] ASC
but when I run this query:
var q = linqMetaData.Products.Select(product => new { ProductName = product.ProductName }).OrderBy(p => p.ProductName);
the generated sql is:
SELECT [LPA_L1].[ProductName] FROM (SELECT [LPLA_1].[ProductName] FROM [Northwind].[dbo].[Products] [LPLA_1] ) [LPA_L1] ORDER BY [LPA_L1].[ProductName] ASC
It seems that if i don't specify the select expression at the end of linq expression, an extra outer sql select statement is generated which may have some negative effect on performance.
That's because you're ordering the projected set of the query you call the OrderBy on.
The problem is that in some queries such as "joins" I can not specify the select at the end of the expression. For Example:
var q = (from p in linqMetaData.Products
join c in linqMetaData.Categories
on p.CategoryId equals c.CategoryId
select new { ProductName = p.ProductName, CategoryName = c.CategoryName }).OrderBy(product => product.ProductName);
generates the following sql statement:
SELECT [LPA_L1].[ProductName], [LPA_L1].[CategoryName] FROM (SELECT DISTINCT [LPA_L2].[ProductName], [LPA_L3].[CategoryName] FROM ( [Northwind].[dbo].[Products] [LPA_L2] INNER JOIN [Northwind].[dbo].[Categories] [LPA_L3] ON [LPA_L2].[CategoryID] = [LPA_L3].[CategoryID])) [LPA_L1] ORDER BY [LPA_L1].[ProductName] ASC
As I remember a few days ago I was testing this scenario against Microsoft Linq to SQL and the generated sql statement was equal in both situations.
Similar answer: because you're ordering on the projected set, the order by is outside the set you're sorting. Linq to sql contains a query optimizer which optimizes these queries sometimes (it's not always possible). Unlike to the linq to sql developers, we're not part of the C# compiler team and therefore we don't have all the inner-documentation used at MS so we first focus on making the linq provider bugfree and then we'll see if we can optimize the queries.
What I don't understand is why you're placing the orderby outside the projected set, if you want to sort that set:
var q = from p in linqMetaData.Products
join c in linqMetaData.Categories
on p.CategoryId equals c.CategoryId
orderby p.ProductName ascending
select new { ProductName = p.ProductName, CategoryName = c.CategoryName};
Same result. The OrderBy outside the projection makes the sequence/query it's called on a derived table. If that query has a projection, it will keep it as a derived table. This is necessary because if that query is a grouped query for example, it can't place the orderby inside the derived table, so it leaves it as-is.
Rule of thumb: if you're concerned with performance, keep everything compact and in the right order. As soon as you are adding extension methods to a set which has a projection, you're actually creating a less efficient query, simply because you're wrapping a query inside a query. This is also mentioned in the documentation: write everything in the right order: from, where, orderby, select etc. This will give the least headaches.
Another problem, If I use one of the business entities generated by LLBL instead of using anonymous types in the previous query I will get an exception.
The linq expression is:
var q = (from p in linqMetaData.Products
join c in linqMetaData.Categories
on p.CategoryId equals c.CategoryId
select new ProductsEntity() {ProductId =p.ProductId, ProductName = p.ProductName, Discontinued=p.Discontinued }).OrderBy(product => product.ProductName);
the exception is:
Invalid column name 'SupplierID'.
Invalid column name 'CategoryID'.
Invalid column name 'QuantityPerUnit'.
Invalid column name 'UnitPrice'.
Invalid column name 'UnitsInStock'.
Invalid column name 'UnitsOnOrder'.
Invalid column name 'ReorderLevel'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'SupplierID'.
Invalid column name 'CategoryID'.
Invalid column name 'QuantityPerUnit'.
Invalid column name 'UnitPrice'.
Invalid column name 'UnitsInStock'.
Invalid column name 'UnitsOnOrder'.
Invalid column name 'ReorderLevel'.
Source Error:
Line 44:
Line 45: GridView1.DataSource = q;
Line 46: GridView1.DataBind();
Line 47:
Line 48: }
[SqlException (0x80131904): Invalid column name 'SupplierID'.
Invalid column name 'CategoryID'.
Invalid column name 'QuantityPerUnit'.
Invalid column name 'UnitPrice'.
Invalid column name 'UnitsInStock'.
Invalid column name 'UnitsOnOrder'.
Invalid column name 'ReorderLevel'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +86
[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'SupplierID'.
Invalid column name 'CategoryID'.
Invalid column name 'QuantityPerUnit'.
Invalid column name 'UnitPrice'.
Invalid column name 'UnitsInStock'.
Invalid column name 'UnitsOnOrder'.
Invalid column name 'ReorderLevel'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +223
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) +147
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +717
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +126
SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute) +201
SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) +181
SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +23
SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +17
SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() +16
SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator() +8
System.Web.UI.WebControls.PagedDataSource.GetEnumerator() +134
System.Web.UI.WebControls.GridView.CreateAutoGeneratedColumns(PagedDataSource dataSource) +274
System.Web.UI.WebControls.GridView.CreateColumns(PagedDataSource dataSource, Boolean useDataSource) +418
System.Web.UI.WebControls.GridView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +640
System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +57
System.Web.UI.WebControls.GridView.PerformDataBinding(IEnumerable data) +14
System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +114
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +31
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
System.Web.UI.WebControls.GridView.DataBind() +4
_Default.Page_Load(Object sender, EventArgs e) in d:\Sample\WebSite4\Default.aspx.cs:46
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627
I guess the exception occurs when I set value only for some of the "ProductsEntity" properties but not all of its properties. It seems that the outer select statement tries to select all the properties of the object but in the inner select query it only selects the requested properties.
All of the above linq expressions are also tested against Oracle database using ODP.NET driver and the behavior is the same as SQL Server.
Interesting scenario. The linq provider sees an entity materialization and tries to produce an entity fetch instead of a projection based fetch. There are two pipelines used: for entities, the entity fetch pipeline is used (which is optimized for fetching entities) and if any other type of fetch is to be done (anonymous type, projection to list, projection to custom class etc.) a projection based fetch is performed. For entities, this is less efficient and it won't use the inheritance logic for TargetPerEntity, (something linq to sql doesn't support ), for entities, so hence we use two pipelines.
Due to this, the query apparently assumes everything is there, which isn't as the set to project from is smaller.
I'll see if I can find a way to work around this. However, if you want to materialize entities, which can use inheritance (so extra tables have to be joined etc.), and you don't want to fetch all fields of these entities, use the ExcludeFields extension method to exclude fields from the set to fetch.
Does ExcludeFields match what you need, or do you have a particular reason why you want to use this particular scenario?
Anyway, I'll try to find a fix for this as well, if possible. It might be next week though