Using v2.6, SelfServicing model against SQLExpress 2008 database, using latest runtime (2.6.09.0511 - the Linq support library version is 2.6.09.0512)

I am getting this error:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled
  Message="An exception was caught during the execution of a retrieval query: The multi-part identifier \"LPLA_4.Line\" could not be bound.\r\nThe multi-part identifier \"LPLA_4.Number\" 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="05112009"
  RuntimeVersion="2.6.0.0"
  QueryExecuted="\r\n\tQuery: SELECT [LPLA_1].[lineID] AS [Line], [LPLA_1].[train_Id] AS [Number] FROM [HoldingArea].[dbo].[train] [LPLA_1]  WHERE ( ( ( ( [LPLA_1].[lineID] LIKE @LineId1)) AND (  EXISTS (SELECT [LPA_L1].[Line] FROM (SELECT [LPLA_5].[lineID] AS [Line], [LPLA_5].[train_Id] AS [Number] FROM [HoldingArea].[dbo].[train] [LPLA_5]  WHERE ( ( NOT ( [LPLA_5].[lineID] LIKE @LineId2)))) [LPA_L1] WHERE ( [LPLA_4].[Line] = [LPA_L1].[Line] AND [LPLA_4].[Number] = [LPA_L1].[Number])))))\r\n\tParameter: @LineId1 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: \"G%\".\r\n\tParameter: @LineId2 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: \"G%\".\r\n"
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IEntityFields fields, IPredicateExpression filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.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 WindowsFormsApplication1.Form1.button1_Click(Object sender, EventArgs e) in C:\Version_Control\back-office\TA-back-office\PisDbGen\Src\Prototype\WindowsFormsApplication1\Form1.cs:line 336
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.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.OnMessage(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 System.Windows.Forms.Application.Run(Form mainForm)
       at WindowsFormsApplication1.Program.Main() in C:\Version_Control\back-office\TA-back-office\PisDbGen\Src\Prototype\WindowsFormsApplication1\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       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_4.Line\" could not be bound.\r\nThe multi-part identifier \"LPLA_4.Number\" could not be bound."
       Source=".Net SqlClient Data Provider"
       ErrorCode=-2146232060
       Class=16
       LineNumber=1
       Number=4104
       Procedure=""
       Server="SOFTWARE11\\SQLEXPRESS2008"
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.SqlInternalConnection.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: 

when using the following code:

LinqMetaData md = new LinqMetaData();           
            var t = from p in md.Train
                    where p.LineId.StartsWith("G")
                    select new
                    {
                        Line = p.LineId,
                        Number = p.TrainId
                    };

            listBox1.DisplayMember = "Line";
            listBox1.DataSource = t.ToList();

            var t2 = from l in md.Train
                     where !l.LineId.StartsWith("G")
                     select new
                     {
                         Line = l.LineId,
                         Number = l.TrainId
                     };
            
            listBox2.DisplayMember = "Line";
            listBox2.DataSource = t2.ToList();

            var t3 = t.Intersect(t2);
            listBox3.DisplayMember = "Line";
            listBox3.DataSource = t3.ToList();   //<== error thrown here

            var t4 = t2.Except(t);
            listBox4.DisplayMember = "Line";
            listBox4.DataSource = t4.ToList();

At this stage, I'm just trying to compare two sets of the same entity type to see whether I get the expected results.

Could you possibly take a look and point me in the right direction?

Ultimately, I want to compare two sets of entities and see whether they match. I was planning on doing this by using t.Except(t2).Union(t2.Except(t)), although I also just found out that LLBLGen doesn't support Union, so I was trying other approaches.

Try:

var t3 = t.ToList();
var t4 = t3.Interserct(t3);

Legend. Thanks.

Still this looks like a bug somewhere, likely in the area where a query is re-used.

Could you give more information about the tables used so we can try to reproduce it?

Sure thing. I'm just using a table where there are two fields, a varchar and an int field. Create any table and that will work, then use the projection style for the query.

I'm selecting some records from each table and trying Intersect and Except on the entities returned (as per the code). There's nothing special about the entities, and I'm not using any relationships or anything.

The reason I didn't call the '.ToList()' was because I tried exactly the same syntax using Linq to Objects in .Net on a couple of strings, and it worked perfectly. When I tried it with LLBLGen, however, it failed until I received the suggestion made above.

Meteor wrote:

Sure thing. I'm just using a table where there are two fields, a varchar and an int field. Create any table and that will work, then use the projection style for the query.

I'm selecting some records from each table and trying Intersect and Except on the entities returned (as per the code). There's nothing special about the entities, and I'm not using any relationships or anything.

The reason I didn't call the '.ToList()' was because I tried exactly the same syntax using Linq to Objects in .Net on a couple of strings, and it worked perfectly. When I tried it with LLBLGen, however, it failed until I received the suggestion made above.

Thanks, I'll try to reproduce it. Keep in mind that the ToList() suggestion given will pull all data to the client, also a reason why we want to fix this, also because except and intersect should work as documented.

Reproduced

[Test]
public void CombinedExceptIntersectUsage()
{
    LinqMetaData metaData = new LinqMetaData();
    var q1 = from c in metaData.Customer
            where c.CompanyName.StartsWith("B")
            select new { c.CustomerId, c.CompanyName };

    var q2 = from c in metaData.Customer
             where !c.CompanyName.StartsWith("B")
             select new { c.CustomerId, c.CompanyName };

    var q3 = q1.Intersect(q2);
    foreach(var v in q3)
    {
        
    }
}

Looking into it.

(edit) for entities it works (so instead of projecting into an anonymous type, do select c), but for custom types it doesn't.

(btw, the query should result in 0 rows so I'm not sure if the query is what you're after, nefvertheless, we'll fix it for you ). The point is that the value you filter on is in the projection so also in the list of values to compare in the intersect, so this leads never to a pair of values which are equal, as set 2 has no row which is in set 1.

Fixed it See attached dll. I now wrap the source query if required in a derived table, so also appended where clauses after the intersect/except (they use the same method) operator are still using the new alias. Also passing pairs of values in intersect/except now have more solid support for this. It worked OK for some cases, but not for all. Now it works for all cases

Yes, I'm aware that the query I've given you is kind of nonsensical. I was doing a rough test to see what I'd get back, planning eventually to compare projections from entities in two different databases, but with the same compare fields. That result will tell me whether I need to insert the list of related records for the entity, or whether the list is already there

Oh, just missed your previous post. Great - I'll grab the dll as soon as I'm back in the office. Thanks for the typical lightning response