I am using the latest build runtime libraries (03172009 & SQL Server 2005) and am getting an error when running the linq query below. It is a many to many relationship where for some reason the sql is not constructed properly. I have tried all sorts of variations with no success.

Thanks in advance for ur help.

public IQueryable<Person> GetAssistantsByPersonId(decimal personId) { return new LinqMetaData(_adapter).OwnerAssistant.Where(p => p.OwnerPersonId == personId).Select(a => new Person { FullName = a.AssistantPerson.FullName, Addresses = a.AssistantPerson.Addresses.Select(b => new Address { AddressTypeCode = b.Address.AddressType.AddressTypeCode, PrimaryPhone = b.Address.PrimaryPhone, PrimaryFax =b.Address.PrimaryFax }) }); }

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException occurred Message="An exception was caught during the execution of a retrieval query: The multi-part identifier \"LPLA_1.assistantPersonId\" could not be bound.\r\nThe multi-part identifier \"LPLA_1.assistantPersonId\" 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="03132009" RuntimeVersion="2.6.0.0" QueryExecuted="\r\n\tQuery: SELECT [LPA_L4].[addressTypeCode] AS [AddressTypeCode], [LPA_L3].[primaryPhone] AS [PrimaryPhone], [LPA_L3].[primaryFax] AS [PrimaryFax], [LPLA_1].[assistantPersonId] AS [AssistantPersonId] FROM ((( [MyDB].[dbo].[Person] [LPA_L1] INNER JOIN [MyDB].[dbo].[PersonAddress] [LPA_L2] ON [LPA_L1].[personId]=[LPA_L2].[personId]) INNER JOIN [MyDB].[dbo].[Address] [LPA_L3] ON [LPA_L3].[addressId]=[LPA_L2].[addressId]) LEFT JOIN [MyDB].[dbo].[AddressType] [LPA_L4] ON [LPA_L4].[addressTypeId]=[LPA_L3].[addressTypeId]) WHERE ( ( ( [LPLA_1].[assistantPersonId] = @AssistantPersonId1)))\r\n\tParameter: @AssistantPersonId1 : Decimal. Length: 0. Precision: 15. Scale: 0. Direction: Input. Value: 533.\r\n" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(I RetrievalQuery queryToExecute, CommandBehavior readerBehavior) at Infrastructure.Data.LLBLGenPro.ORM.DatabaseSpecific.Data AccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in ... aAccess\LLBLGenPro\DatabaseSpecific\DataAccessAdapter.cs:line 292 InnerException: System.Data.SqlClient.SqlException Message="The multi-part identifier \"LPLA_1.assistantPersonId\" could not be bound.\r\nThe multi-part identifier \"LPLA_1.assistantPersonId\" could not be bound." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=4104 Procedure="" 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(TdsParserStateO bject 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(Comman dBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) InnerException:

Please post relevant table/entity information, we can't reproduce it otherwise.

Also, please try to make the query smaller so we have a simple query to look at, the bigger the query the more complex it becomes to find the cause of this.

Your query is inside a method. If you're padding additional operators to this query, we need the FULL picture. Your query is a nested query and we need to know how things are executed. the exception contains the faulty sql but that's not enough to track this down.

Ok hopefully this should be enough to reproduce on your side:

using System.Collections.Generic;
using System.Linq;
using Llblgen.DatabaseSpecific;
using Llblgen.Linq;

namespace ConsoleApplication9
{
    class Program
    {

        static void Main(string[] args)
        {
            var test = GetAssistantsByPersonId(2).ToList();
        }

        public static IQueryable<Person> GetAssistantsByPersonId(decimal personId)
        {
            var adapter = new DataAccessAdapter(@"Data Source=.;Initial Catalog=Test;Integrated Security=True");

            var test = new LinqMetaData(adapter).OwnerAssistant.Where(p => p.OwnerId == personId).Select(a => new Person
            {
                PersonId = a.AssistantPerson.PersonId,
                FullName = a.AssistantPerson.FullName,
                Addresses = a.AssistantPerson.Addresses.Select(b => new Address
                {
                    AddressTypeCode = b.Address.AddressType.AddressTypeCode,
                    //PrimaryPhone = b.Address.PrimaryPhone,
                    //PrimaryFax = b.Address.PrimaryFax
                })
            });

            return test;
        }

        public class Person
        {
            public int PersonId { get; set; }
            public string FullName { get; set; }
            public IEnumerable<Address> Addresses { get; set; }
        }

        public class Address
        {
            public string AddressTypeCode { get; set; }
        }

    }
}

Attached is a zip file containing sql scripts and the .lgp file

Thanks, will look into it.

I've removed the attachment (after downloading) as this is a public forum and the lgp file might contain security / other info.

Thanks, using your project, I could rebuild the query and reproduce the error in our testsuite on adventureworks/northwind:

LinqMetaData metaData = new LinqMetaData(adapter);
var q = metaData.Order.Where(o => o.OrderId == 10254).Select(o =>
                            new Customer
                            {
                                CustId = o.Customer.CustomerId,
                                CompanyNme = o.Customer.CompanyName,
                                Employees = o.Customer.EmployeeCollectionViaOrder.Select(e => new Employee()
                                {
                                    EmployeeId = e.EmployeeId,
                                    FirstName = e.FirstName,
                                    LastName = e.LastName
                                })
                            });

As soon as I add the employee m:n subquery, it fails. Looking into this. Could be due to the custom projections nested into eachother...

Cool. I hope their is a fix for it . It is quite a common scenario in my current project. I also tried a number of variations to try to get it working with no luck.

nabils wrote:

Cool. I hope their is a fix for it . It is quite a common scenario in my current project. I also tried a number of variations to try to get it working with no luck.

I think this is a bridge too far at the moment. It's not the custom types, but the way things are related to eachother. You have two queries: 1) OwnerAssistant.Where(p => p.OwnerPersonId == personId).Select(a =>new Person { FullName = a.AssistantPerson.FullName, List });

and 2) a.AssistantPerson.Addresses.Select(b => new Address { AddressTypeCode = b.Address.AddressType.AddressTypeCode, PrimaryPhone = b.Address.PrimaryPhone, PrimaryFax =b.Address.PrimaryFax });

The provider first executes the first query, having a dummy value at the 'list' spot (will be replaced by the results of query 2 after the merge). It then tries to construct the second one, however as the two sets have to be merged into eachother, it tries to construct a correlation filter. This filter has to tie a row or rows in query 2 with a row in query 1.

The elements returned from query 2 are not related to the elements in query 1, only indirectly via other elements. So if I have an OwnerPerson instance and a Address instance, I can't tie them together directly, there's no direct relationship. So I can't connect a row in the set of query 2 to an element returned by query 1. The query produced by query 2 therefore is incorrect, it misses information: the filter isn't applied to the right element, however as other situations aren't supported/possible, it tries to do the best it can, but of course in a query like you specified, it's not possible to do so.

This happens in our provider and not in linq to sql for example because we execute nested queries as separate sets and try to merge them together efficiently. Linq to sql for example executes a nested query per hosting element, causing many many queries to be executed, which is very slow, however they always work.

As the situation which your query describes isn't very common (as it nests indirectly related information into eachother as if it's directly related) we opted for the huge performance benefit, of course this trade off isn't beneficial for everybody, like your situation is hurt by this decision.

It is possible to solve this in some cases, as query 2 should return the field to compare with in the set of query 1 which would automatically tie rows from query 2 to the rows in query 1. However to accomplish this, a lot of code has to be added, and we cut this from v2.6 to get the provider done in time. The reason for the huge pile of code is that the query has to be analyzed to see if the field(s) to compare with are obtainable from query 2. If it was simple I'd have added it for you today, however it will take days if not weeks to add this, so we postponed this for v3 (which isn't released very soon so I have no good news for you, I'm afraid).

Sorry to have this news for you. it's the second time I have to disappoint you in this. There are workarounds like executing the queries separately, but these are obviously not optimal.

About it being quite common: the scenario of your query relates indirect related info directly to eachother. Is this due to over normalization or other situations? I also see you have a cycle in your entities (Person - PersonAddress, they point to eachother).

What I wonder is if the m:1 relationships aren't really 1:1 relationships so you could use inheritance perhaps (OwnerAssistant - Person, as OwnerAssistant is-a Person, it seems a bit odd 1 Person entity instance is related to more than 1 OwnerAssistant entity )

Oh well. I guess I'll have to do it the long way!!! Thanks for your help though. I had a good look at the relationships and they are all there for a reason. The db is extremely normalised. It definitely would be useful to have this capability as an option even though it does impact performance. Looking forward to v3

nabils wrote:

Oh well. I guess I'll have to do it the long way!!! Thanks for your help though. I had a good look at the relationships and they are all there for a reason. The db is extremely normalised. It definitely would be useful to have this capability as an option even though it does impact performance. Looking forward to v3

The cycle in the relationships could hurt your application, e.g. you can't save a graph with both entities pointing towards themselves. So if you run into this, that's the cause

Thanks for the warning. I'll keep it in mind. How else would I handle this? Any ideas?? I have a "person" table containing employees then another table containing 2 colums containing id's referencing people in the persons table. One column is assistantid and the other is employeeid. This is a many to many relationship: an employee can have multiple assistants and an assistant can assist multiple employees.

nabils wrote:

Thanks for the warning. I'll keep it in mind. How else would I handle this? Any ideas?? I have a "person" table containing employees then another table containing 2 colums containing id's referencing people in the persons table. One column is assistantid and the other is employeeid. This is a many to many relationship: an employee can have multiple assistants and an assistant can assist multiple employees.

No that's not the one I meant the PersonAddress <-> Person cycle. PersonAddress points to Person and Person points to PersonAddress both with an FK. So if you have a PersonAddress entity which refers to a Person entity you can't have the Person refer to the same PersonAddress entity and save both at the same time (inserts) as this gives a catch 22: as both point to eachother you have to insert NULL in 1 of the FK fields in one of the entities.

Oh sorry about that!!! My bad. I had accidentally created an extra relationship. There should only be one 1:n from Person to PersonAddress.

Oh sorry about that!!! My bad. I had accidentally created an extra relationship. There should only be one 1:n from Person to PersonAddress

Does this sort this thread out ?

Yes it does. Thanks

I have the same problem but this doesn't seem to fix it for me.

matt.whitby wrote:

I have the same problem but this doesn't seem to fix it for me.

please open a new thread with your info and your problem description and your query's specifics.