This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi all, Tested with both the nightly from a few days ago, and the 4.0. release. Query:
coupon is a reference to an entity (Type: ActionCode). The ActionCode has a foreign key to Reservation. Becomes SQL Query: SELECT SUM(Invoices.TotalAmount) FROM [Reservations] WHERE [Reservations].[ActionCodeId] = '5866f494-503e-495c-aaab-3e01cb5fcdbc' This obviously can never work, since the FK ref is the other way around. Result: [SqlException (0x80131904): The multi-part identifier "Invoices.TotalAmount" could not be bound.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2073550 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064508 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +86 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987 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.ExecuteScalar() +139 ..() +34 ..(IUnitOfWork , IDbCommand , ) +514 ..(IUnitOfWork , IDbCommand ) +125 ..() +299 ..(String , IdentifierExpression , Query ) +542 Mindscape.LightSpeed.UnitOfWork.Calculate(String calculation, IdentifierExpression attribute, Query query) +133 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType) +993 Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) +315 Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +43 Version that works:
Another version that works:
The SQL query for the inner join version that works: SELECT SUM(t2.TotalAmount) FROM [Reservations] [t0] INNER JOIN [ActionCode] [t1] ON [t0].[ActionCodeId] = [t1].[Id] INNER JOIN [Invoices] [t2] ON [t0].[Id] = [t2].[ReservationId] This obviously works, and is pretty doable for now, but I just wanted to report this. I had the multi-part identifier problem before and sometimes it is not directly obvious what the reason is. Is this something that Lightspeed will support in the future (detect which way the FK is, and inverse to an inner join)? |
|
|
Thanks for alerting us to this. Just to clarify, Reservation.ActionCode is a one-to-one association, right? |
|
|
one of these days I will make a proper bug report, containing all details... The relationship between ActionCode and Reservation is 1..n. The ActionCode is an active coupon code that gives discount on making a reservation, one coupon code can have many reservations linked to it since it is used in news letters, printed material as a marketing device. So, Reservation has the FK (ActionCodeId (FK, uniqueidentifier, null). Cheers! |
|
|
Hmm, a similar seems to be working okay for me.
So there must be something about your model that I'm still missing. Could you provide us with a minimal repro project, just the ActionCode and Reservation entities and a NUnit test or console application that exhibits the error? Thanks! (Alternatively, if you're happy enough with the workaround and you don't want to spend the time to create a repro, let us know and we can shelve the issue for now.) |
|