Hi, I am trying to concat two fields in a subquery. But the prepared sql statement does not contain table name. I duplicate the same error against Northwind database i'm using the lastest build. Any ideas what i'm doing wrong?

cheers

version numbers SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 2.6.9.1005 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll 2.6.9.1008

using (DataAccessAdapter dap = new { LinqMetaData meta = new LinqMetaData(dap);

 var r = (from o in meta.Orders select new {
             cid = o.CustomerId,
             customer = (from c in meta.Customers where c.CustomerId == o.CustomerId select c.ContactTitle ?? "" + " " + c.ContactName ).First(),
            }).ToList();

}

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled
  Message="An exception was caught during the execution of a retrieval query: The multi-part identifier \"LPLA_2.CustomerID\" could not be bound.
  The multi-part identifier \"LPLA_2.ContactTitle\" could not be bound.\r\nThe multi-part identifier \"LPLA_2.ContactTitle\" could not be bound.
  The multi-part identifier \"LPLA_2.ContactName\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to 
  examine the cause of this exception."
  Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"
  RuntimeBuild="10052009"
  RuntimeVersion="2.6.0.0"
  QueryExecuted="\r\n\tQuery: SELECT [LPLA_1].[CustomerID] AS [cid], (SELECT TOP 1 COALESCE([LPLA_2].[ContactTitle], (@LO162cb77e1 + [LPLA_2].[ContactName]))
  AS [LPFA_2] WHERE ( ( ( ( [LPLA_2].[CustomerID] = [LPLA_1].[CustomerID]))))) AS [customer] FROM [NORTHWND].[dbo].[Orders] [LPLA_1] 
  tParameter: @LO162cb77e1 : String. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \" \".\r\n"
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
       at Northwnd.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in C:\Projects\dotNet\Northwnd\DatabaseSpecific\DataAccessAdapter.cs:line 293
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, 
       Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at Test.Form1.button1_Click(Object sender, EventArgs e) in C:\Projects\dotNet\Northwnd\North\Test\Form1.cs:line 27
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at Test.Program.Main() in C:\Projects\dotNet\Northwnd\North\Test\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       Message="The multi-part identifier \"LPLA_2.CustomerID\" could not be bound.\r\nThe multi-part identifier \"LPLA_2.ContactTitle\" could not be bound.\r\n
       The multi-part identifier \"LPLA_2.ContactTitle\" could not be bound.\r\nThe multi-part identifier \"LPLA_2.ContactName\" could not be bound."

       Source=".Net SqlClient Data Provider"
       ErrorCode=-2146232060
       Class=16
       LineNumber=1
       Number=4104
       Procedure=""
       Server="torik"
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
            at System.Data.SqlClient.SqlDataReader.get_MetaData()
            at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       InnerException: 

select c.ContactTitle ?? "" + " " + c.ContactName

Use string.Concat() function, and put the result in a new variable -> Select new {name = string.Concat(...)}

Walaa wrote:

select c.ContactTitle ?? "" + " " + c.ContactName

Use string.Concat() function, and put the result in a new variable -> Select new {name = string.Concat(...)}

Now i'm getting "Method call to 'Concat' doesn't have a known mapped database function or other known handler." i checked the manual string.Concat is a pre-mapped function. what i'm missing?

Now i'm getting "Method call to 'Concat' doesn't have a known mapped database function or other known handler." i checked the manual string.Concat is a pre-mapped function. what i'm missing?

Strange indeed, it's working fine over here.

The following is working fine against Northwind database:

var q = from c in metaData.Customers                    
                     select new {name = string.Concat(c.ContactTitle, c.ContactName)};

Can you try something as simple as the above?

Walaa wrote:

Now i'm getting "Method call to 'Concat' doesn't have a known mapped database function or other known handler." i checked the manual string.Concat is a pre-mapped function. what i'm missing?

Strange indeed, it's working fine over here.

The following is working fine against Northwind database:

var q = from c in metaData.Customers                    
                     select new {name = string.Concat(c.ContactTitle, c.ContactName)};

Can you try something as simple as the above?

Works as expected

SqlProfiler output as

SELECT ([LPLA_1].[ContactTitle] + [LPLA_1].[ContactName]) AS [name] FROM [NORTHWND].[dbo].[Customers] [LPLA_1]

Would you please post the failed linq code? (simplified as possible to focus on the Concat issue).

Walaa wrote:

Would you please post the failed linq code? (simplified as possible to focus on the Concat issue).

using (DataAccessAdapter dap = new DataAccessAdapter()) {

            LinqMetaData meta = new LinqMetaData(dap);

            var r = (from o in meta.Orders
                     select new
                     {
                         cid = o.CustomerId,
                         customer = (from c in meta.Customers where c.CustomerId == o.CustomerId select new { A = string.Concat(c.ContactTitle ?? "", " ", c.ContactName) }).First().A,
                     }).ToList();

        }

The function mapping of the string.Concat() method, only mapps the override that accepts 2 parameters only.

So if I would modify my Northwind example to concatenate 3 paramaters, I would do it this way:

var q = from c in metaData.Customers
                    select new { name = string.Concat(string.Concat(c.ContactTitle, " "), c.ContactName) };

Could you please make sure the lines are not unnecessary long so the thread is unnecessary wide? Thanks -- Otis

Walaa wrote:

The function mapping of the string.Concat() method, only mapps the override that accepts 2 parameters only.

So if I would modify my Northwind example to concatenate 3 paramaters, I would do it this way:

var q = from c in metaData.Customers
                    select new { name = string.Concat(string.Concat(c.ContactTitle, " "), c.ContactName) };

The second linq works as you mentioned but the first one gives the exception below any ideas?

   var r = (from o in meta.Orders
                         select new
                         {
                             cid = o.CustomerId,
                             customer = (from c in meta.Customers where c.CustomerId == o.CustomerId 
                                         select new { 
                                             A = string.Concat(string.Concat(c.ContactTitle ?? "", " "), c.ContactName ?? "") 
                                         }).First().A,
                         }).ToList();

                var q = (from c in meta.Customers
                        select new { name = string.Concat(string.Concat(c.ContactTitle ?? "" ," "), c.ContactName ?? "") }).First().name;

System.InvalidCastException was unhandled
  Message="Unable to cast object of type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SelectExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetReferenceExpression'."
  Source="SD.LLBLGen.Pro.LinqSupportClasses.NET35"
  StackTrace:
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.MemberAccessEvaluator.HandleMemberIsPartOfSet(MemberExpression expressionToHandle, Expression memberContainer, SetAlias memberAlias)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.MemberAccessEvaluator.HandleMemberIsPartOfSelect(MemberExpression expressionToHandle, SelectExpression memberContainer, SetAlias memberAlias)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.MemberAccessEvaluator.HandleMemberExpression(MemberExpression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpressionList(ReadOnlyCollection`1 listToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleNewExpression(NewExpression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleProjectionExpression(ProjectionExpression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at Test.Form1.button1_Click(Object sender, EventArgs e) in C:\Projects\dotNet\Northwnd\North\Test\Form1.cs:line 30
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at Test.Program.Main() in C:\Projects\dotNet\Northwnd\North\Test\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Reproduced with latest RTL.

RTL 2.6.9.1005

Code

var q = (from o in metaData.Order
        select new
        {
            cid = o.CustomerId,
            cust = (from c in metaData.Customers
                    where c.CustomerId == o.CustomerId
                    select new {
                        A = string.Concat(string.Concat(c.ContactTitle, c.ContactName), c.CompanyName)
                    }).First().A
        }).ToList();

Exception message

Unable to cast object of type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SelectExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetReferenceExpression'.

Stack trace Attached.

We will look into it. In the meantime please use this: Workaround. Not use the property after the First() call:

var q = (from o in metaData.Order
        select new
        {
            cid = o.CustomerId,
            cust = (from c in metaData.Customers
                    where c.CustomerId == o.CustomerId
                    select new {
                        A = string.Concat(string.Concat(c.ContactTitle, c.ContactName), c.CompanyName)
                    }).First()
        }).ToList();

daelmo wrote:

Reproduced with latest RTL.

Workaround. Not use the property after the First() call:

Thanks this solved my problem

The initial problem (the exception which was reproduced by david) is fixed now. It's in the hotfix attached to this post: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=94098&ThreadID=16807

as it fixes 1 other problem as well.