LINQ SubQuery Errors

[Bret]

I'm trying to execute a LINQ that has a subquery containing a aggregate function. I've created a class that inherits the generated table class from the ORM model so that I can store this needed information without altering my model. I've tried creating the query other was but this seems to be the cleanest thus far. Following is our code block:

var qry3 = from op in dbModel.ORDERED_PAYMENTs

  select new OPcls{

               ORDERED_PAYMENTS_KEY = op.ORDERED_PAYMENTS_KEY,

               //... other stuff

               StatusDate = (from opsh in dbModel.ORDERED_PAY_Status_HISTORIES

                        where opsh.ORDER_PAYMENTS_KEY == op.ORDERED_PAYMENTS_KEY

                        select opsh.STATUS_DATE).Max()};

 public class OPcls : ORDERED_PAYMENTS

    {

        private DateTime? _statusDate;

        public DateTime? StatusDate{

             

            get{

                return _statusDate;

            }

            set{

                _statusDate = value;

            }

        }

    

This is the error I'm receiving:
Error executing query: Telerik.OpenAccess.RT.sql.SQLException: The multi-part identifier "c.ORDERED_PAYMENTS_KEY" could not be bound.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
   at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
SQL:
SELECT a.[ORDERED_PAYMENTS_KEY] AS COL1,  (SELECT MAX(b.[STATUS_DATE])                  FROM [ORDERED_PAY_STATUS_HISTORIES] b WHERE b.[ORDER_PAYMENTS_KEY] = c.[ORDERED_PAYMENTS_KEY])  AS  EXPR2 FROM [ORDERED_PAYMENTS] a  Telerik.OpenAccess.RT.sql.SQLException: The multi-part identifier "c.ORDERED_PAYMENTS_KEY" could not be bound.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
   at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()

If I remove the OPcls from the select clause, the query will work however i'm left with an anonymous type and that poses more issues.  Any help is appreciated!

Thanks,
Bret

[Thomas]

Hi Bret,

 I will have a look into this tomorrow.

Best wishes,
Thomas
the Telerik team

[Thomas]

Hi Bret,

good news: the issue was fixed in the mean time and the next version will contain the needed bits.

Best wishes,
Thomas
the Telerik team

[Amir]

Was the fix for this included in the latest Sitefinity version?  I'm having the same issue. If not, is there a PITS number, or an anticipated time to include this in there?

[Serge]

Hello Amir,

 The fix will be included in the next internal build that will most probably be released by the end of this week. Unfortunately that would mean that it will be included in the next Sitefinity version as well. At the moment there isn't a pits issue related to this problem. 

A possible solution would be to use assembly redirection in order to make your Sitefinity application work with the new assemblies.

I hope this helps.

Kind regards,
Serge
the Telerik team

[Greg]

Definitely not fixed

Samplq LINQ query:

var q = new NorthwindModel().Products.AsQueryable();
short quant = 1;
q = q.Where(p =>
(
    from y in db.Products
    where y == p
    join od in db.OrderDetails on new { y.ProductID, Quantity = quant } equals new { od.ProductID, od.Quantity } into odg
    from od in odg.DefaultIfEmpty()
    where od.Discount > 0
    select 1
).Any());

SQL:

SELECT ...
FROM [Products] a
LEFT JOIN [Order Details] AS b ON (? = b.[Quantity] AND c.[ProductID] = b.[OrderID])
WHERE EXISTS (SELECT 1
FROM [Products] c
WHERE c.[ProductID] = a.[ProductID]
AND b.[Discount] > 0)
ORDER BY COL1

Exception message: The multi-part identifier "c.ProductID" could not be bound.
As you can see the OrderDetails extent is moved outside the subquery but still references the Products extent inside it. This only happens when the join condition contains both constants and fields from the Products extent.

[Thomas]

Hi Greg,

you are right: this join form was not fixed. However, based on your good reproduction I could fix this now.
A new version is due in 2-3 weeks.

Thanks for reporting this issue again, I've updated your Telerik points.
Regards,
Thomas
Telerik

 

 

[Nils C.]

I have another one, which is not workin:

Error executing query: Telerik.OpenAccess.RT.sql.SQLException: The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

Statement(s) could not be prepared. ---> System.Data.SqlClient.SqlException: The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

Statement(s) could not be prepared.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

   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.ExecuteReader(CommandBehavior behavior)

   at OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior)

   at Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.ExecuteReader()

   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader()

   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)

   --- End of inner exception stack trace ---

   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)

   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery(Nullable`1 commandTimeout)

   at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()

SQL:

SELECT a.[id] AS COL1, b.[Datenfeld_LeadID] AS COL2, b.[id] AS COL3, b.[Datenfeld_AnsprechpartnerID] AS COL4, b.[Datenfeld_ArticleId] AS COL5, b.[Datenfeld_ArticleVersionId] AS COL6, b.[Datenfeld_BenutzerID] AS COL7, b.[CompanyTyp] AS COL8, b.[Datenfeld_DateiProjekteId] AS COL9, b.[Datenfeld_AnsprechpartnerID] AS COL10, b.[Datenfeld_ArticleId] AS COL11, b.[Datenfeld_ArticleVersionId] AS COL12, b.[Datenfeld_AufgabeID] AS COL13, b.[Datenfeld_BenutzerID] AS COL14, b.[Datenfeld_DateiProjekteId] AS COL15, b.[Datenfeld_Inhalt] AS COL16, b.[Datenfeld_KundeID] AS COL17, b.[Datenfeld_LeadID] AS COL18, b.[Datenfeld_PhaseID] AS COL19, b.[Datenfeld_ProjektID] AS COL20, b.[Datenfeld_Rechtestufe] AS COL21, b.[Datenfeld_Rechtestufe_lesen] AS COL22, b.[StandardfeldID] AS COL23, b.[Datenfeld_Systemfeld] AS COL24, b.[Datenfeld_Typ] AS COL25, b.[Datenfeld_KundeID] AS COL26, b.[Datenfeld_LeadID] AS COL27, b.[Datenfeld_ProjektID] AS COL28, b.[Datenfeld_ProjektID] AS COL29, b.[StandardfeldID] AS COL30 FROM [Leads] a LEFT JOIN [Datenfeld] AS b ON (a.[id] = b.[Datenfeld_LeadID]) WHERE not (a.[id] IS NULL) AND (c.[vertriebsaktion_id] IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND ((not (a.[verantwortlicher_id] IS NULL) AND (a.[verantwortlicher_id] IN (?)) OR ? = 1 AND a.[verantwortlicher_id] IS NULL)) AND ((a.[lead_status] IS NULL OR (a.[lead_status] IN (?)))) ORDER BY COL1, COL2  Telerik.OpenAccess.RT.sql.SQLException: The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

Statement(s) could not be prepared. ---> System.Data.SqlClient.SqlException: The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

The multi-part identifier "c.vertriebsaktion_id" could not be bound.

Statement(s) could not be prepared.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

   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.ExecuteReader(CommandBehavior behavior)

   at OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior)

   at Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.ExecuteReader()

   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader()

   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)

   --- End of inner exception stack trace ---

   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)

   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery(Nullable`1 commandTimeout)

   at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()

By running this query:

Dim fetchStrategy As New Telerik.OpenAccess.FetchOptimization.FetchStrategy()

fetchStrategy.LoadWith(Of Leads)(Function(c) c.Projekt)
fetchStrategy.LoadWith(Of Leads)(Function(c) c.Ansprechpartner_von_Projekt_oder_Leads)
fetchStrategy.LoadWith(Of Ansprechpartner_von_Projekt_oder_Lead)(Function(c) c.Ansprechpartner)
fetchStrategy.LoadWith(Of Leads)(Function(c) c.Kunde)
fetchStrategy.LoadWith(Of Leads)(Function(c) c.Lead_Stati)
 fetchStrategy.LoadWith(Of Leads)(Function(c) c.Datenfelds)
_db.FetchStrategy = fetchStrategy



'Vertriebsaktionen Filtern
Dim leads_filter4 = (From it In _db.LeadsSubsystemAware Join vt In _db.Gehoert_zu_Vertriebsaktion.Where(Function(x) vt_action_search_items.Contains(x.Vertriebsaktion_id)) On it.Id Equals vt.Lead_id _
Where (Not it.Verantwortlicher_id Is Nothing AndAlso verantwortliche_array.Contains(it.Verantwortlicher_id) _
                     Or (show_empty_value_verantwortlicher = True And it.Verantwortlicher_id.HasValue = False)) And (it.Lead_status Is Nothing OrElse status_array.Contains(it.Lead_status)) Select it).Distinct.ToList

it just happens, if i use this line:          fetchStrategy.LoadWith(Of Leads)(Function(c) c.Datenfelds)

Without that fetchstrategy all is working fine. 

[Thomas]

Hello Nils,

which OpenAccess version are you using ? I will try to repro this here with the newest one...

Regards,
Thomas
Telerik

 

 

[Nils C.]

Hi, 

we are using 2014.2.617.2

[Thomas]

Hello Nils,

I tried to reproduce this here and either my version does no longer contain the issue or my model and data does not match yours. Could you please send us the .rlinq or a small reproduction?

Thanks for your efforts - Regards,
Thomas
Telerik

 

 

[Bob]

Hello Thomas,

We are on version 2013.3.1014.1 of OpenAccess.

We are experiencing the same issue as Nils.  Below is our code.  If we only include one child type of TechTicket in the FetchStrategy the query runs without any errors.  However, if we include a second child type in the FetchStrategy it returns the following error:

Error executing query: Telerik.OpenAccess.RT.sql.SQLException: The multi-part identifier "c.data_origin" could not be bound.

CODE:

//make sure the CustomerEquipment and Equipment associated rows are also loaded with the TechTicket (prevents lazy loading)
FetchStrategy fetchStrategy = new FetchStrategy();
//there seems to be a bug in Telerik Open Access because we are
//unable to eager load more than one child type of TechTicket so had to remove all but one
//decided to leave as TechTicketAssigns but would love to eager load TechnicianNotes as well
fetchStrategy.LoadWith<TechTicket>(tt => tt.TechTicketAssigns);
//fetchStrategy.LoadWith<TechTicket>(tt => tt.TechnicianNotes);
fetchStrategy.LoadWith<TechTicket>(tt => tt.CustomerEquipment);
fetchStrategy.LoadWith<CustomerEquipment>(ce => ce.Equipment);

var tickets = from tt in this.DbContext.TechTickets.Where(tt => tt.Active.Value).LoadWith(fetchStrategy)
              join c in this.DbContext.Customers.Where(c => c.DataOrigin == CurrentAppContext.CurrentIdentity.DataOrigin)
              on tt.CustomerEquipment.CustomerId equals c.CustomerId
              select tt;

return tickets.ToList();

[Bob]

Note also the CustomerEquipment type included in the Fetch Strategy is not a child type of TechTicket but rather a parent type.

[Thomas]

Hi Bob,

would you be able to check with the newest version? I remember that we've fixed an issue in this area, and chances are, the problem will no longer be present.

Regards,
Thomas
Telerik

 

 

[Nils C.]

i just tested it with the newest version. the problem is still there...

[Bob]

Unfortunately, we are currently stuck on version 2013.3.1014.1 because we are using the OpenAccess version that comes with our version of Sitefinity.  We need to try and keep with using the version tied to SiteFinity.

Apparently this issue is still occurring with Nils on the latest version.

Thanks in advance...Bob Baldwin
Trabon

[Thomas]

Yes, there still seems to be an issue when applying a fetch plan on a query which contains already a join. The second, parallel, fetch will not be able to pull in the join and will produce a 'multipart identifier could not be bound' error. This does not occur for the first collection that gets joined in.
We will try to fix that in one of our next versions; thanks for reporting this again.

As a workaround for your special case you could use

var tickets = from tt in this.DbContext.TechTickets
                          .Where(tt => tt.Active.Value)
                          .LoadWith(fetchStrategy)
              .Where(tt => this.DbContext.Customers.Where(c => c.DataOrigin == CurrentAppContext.CurrentIdentity.DataOrigin && tt.CustomerEquipment.CustomerId == c.CustomerId).Any())

       select tt;

This will reformulate the filter done by the join into an .Any(), which is then handled correctly even for the parallel executed statement.

Regards,
Thomas
Telerik