Error converting SQL to LINQ: "Identifier is not a parameter or variable or field of xxx"

[Dan]

I have a SQL query that I'm trying to covert to LINQ to run against a Telerik Open Access database.  I keep receiving errors when I run the LINQ version.  I had thought that the problem was that certain fields in the tables had trailing spaces so I used a Trim() function, but that produced more errors.  I tried breaking my query into separate LINQ queries, but it did not produce the same results as my SQL query.  I tried submitting a Telerik help desk ticket, but they said support is no longer available.  So my questions are:

1.  Is there anyway just to use my SQL query?

Someone on StackOverflow suggested this link: Telerik Data Access, but (1.) it seems to be for Telerik Data Access, not Telerik Open Access, (2.) I'm not sure how to convert the syntax mentioned at that link into a List object like I use in the rest of my LINQ queries, (3.) I've always been told to use LINQ to query Open Access; is this method safe/recommended?

Here is my SQL query:

SELECT DISTINCT us2.ccustno, us2.dispname, us2.csiteno, so.s1_name
FROM [DALubeDeacom].[dbo].[dmbill] bi
      INNER JOIN [DALubeDeacom].[dbo].[dmso1] so
            ON bi.bi_s1id = so.s1_id
      INNER JOIN [DALubeNew].[dbo].[usersecurity] us2
            ON so.s1_name = us2.cparentno
WHERE
        us2.ctype = 'JOBSITE'
    AND us2.csiteno is not null
    AND us2.csiteno != ''
    AND bi.bi_smid = '22'
ORDER BY us2.csiteno

2.  How do I convert my SQL query to LINQ?

 

I tried this query, but it produced the error "Identifier 'Ctype' is not a parameter or variable or field of 'DALube_DeacomModel.Dmbill'." (see full stack trace below).  What does this error mean and how do I fix it?

var query = (from bill in deacom.Dmbills
             join so in deacom.Dmso1 on bill.Bi_s1id equals so.S1_id
             join us in dbContext.Usersecurities on so.S1_name equals us.Cparentno
             where
                 us.Ctype == "JOBSITE"
                 &&
                 us.Csiteno != null
                 &&
                 us.Csiteno != string.Empty
                 &&
                 bill.Bi_smid == iSalesNo
             select new
             {
                 ccustno = us.Ccustno,
                 dispname = us.Dispname,
                 csiteno = us.Csiteno,
                 s1_name = so.S1_name
             }).Distinct();

 

When I run this code I get this error message:

Server Error in '/' Application.

Identifier 'Ctype' is not a parameter or variable or field of 'DALube_DeacomModel.Dmbill'. If 'Ctype' is a property please add the FieldAlias or Storage attribute to it or declare it as a field's alias.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Telerik.OpenAccess.OpenAccessException: Identifier 'Ctype' is not a parameter or variable or field of 'DALube_DeacomModel.Dmbill'. If 'Ctype' is a property please add the FieldAlias or Storage attribute to it or declare it as a field's alias.

Source Error:


Line 873:            catch (Exception ex)
Line 874:            {
Line 875:                throw ex;
Line 876:            }
Line 877:

Source File: c:\inetpub\wwwroot\DA_Lube_CLS\DALubeBarcode\App_Code\DataAccess.cs    Line: 875

Stack Trace:


[OpenAccessException: Identifier 'Ctype' is not a parameter or variable or field of 'DALube_DeacomModel.Dmbill'. If 'Ctype' is a property please add the FieldAlias or Storage attribute to it or declare it as a field's alias.]
   Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type resultType, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid) +3148
   Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid) +97

[InvalidOperationException: An exception occurred during the execution of 'Extent<DALube_DeacomModel.Dmbill>().Join(Extent<DALube_DeacomModel.Dmso1>(), bill => bill.Bi_s1id, so => so.S1_id, (bill, so) => new <>f__AnonymousType32`2(bill = bill, so = so)).Join(Extent<DALubeBarcodeData.Usersecurity>(), <>h__TransparentIdentifier6c => <>h__TransparentIdentifier6c.so.S1_name, us => us.Cparentno, (<>h__TransparentIdentifier6c, us) => new <>f__AnonymousType33`2(<>h__TransparentIdentifier6c = <>h__TransparentIdentifier6c, us = us)).Where(<>h__TransparentIdentifier6d => ((((<>h__TransparentIdentifier6d.us.Ctype == "JOBSITE") AndAlso (<>h__TransparentIdentifier6d.us.Csiteno != null)) AndAlso (<>h__TransparentIdentifier6d.us.Csiteno != String.Empty)) AndAlso (<>h__TransparentIdentifier6d.<>h__TransparentIdentifier6c.bill.Bi_smid == value(DALubeBarcode.App_Code.DataAccess+<>c__DisplayClass6e).iSalesNo))).Select(<>h__TransparentIdentifier6d => new <>f__AnonymousType34`4(ccustno = <>h__TransparentIdentifier6d.us.Ccustno, dispname = <>h__TransparentIdentifier6d.us.Dispname, csiteno = <>h__TransparentIdentifier6d.us.Csiteno, s1_name = <>h__TransparentIdentifier6d.<>h__TransparentIdentifier6c.so.S1_name)).Distinct()'. Failure: Identifier 'Ctype' is not a parameter or variable or field of 'DALube_DeacomModel.Dmbill'. If 'Ctype' is a property please add the FieldAlias or Storage attribute to it or declare it as a field's alias.
See InnerException for more details.
Complete Expression:
.Call System.Linq.Queryable.Distinct(.Call System.Linq.Queryable.Select(
        .Call System.Linq.Queryable.Where(
            .Call System.Linq.Queryable.Join(
                .Call System.Linq.Queryable.Join(
                    .Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[DALube_DeacomModel.Dmbill]>(Extent<DALube_DeacomModel.Dmbill>()),
                    .Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[DALube_DeacomModel.Dmso1]>(Extent<DALube_DeacomModel.Dmso1>()),
                    '(.Lambda #Lambda1<System.Func`2[DALube_DeacomModel.Dmbill,System.Int32]>),
                    '(.Lambda #Lambda2<System.Func`2[DALube_DeacomModel.Dmso1,System.Int32]>),
                    '(.Lambda #Lambda3<System.Func`3[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1,<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1]]>))
                ,
                .Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[DALubeBarcodeData.Usersecurity]>(Extent<DALubeBarcodeData.Usersecurity>()),
                '(.Lambda #Lambda4<System.Func`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],System.String]>),
                '(.Lambda #Lambda5<System.Func`2[DALubeBarcodeData.Usersecurity,System.String]>),
                '(.Lambda #Lambda6<System.Func`3[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity,<>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity]]>))
            ,
            '(.Lambda #Lambda7<System.Func`2[<>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity],System.Boolean]>))
        ,
        '(.Lambda #Lambda8<System.Func`2[<>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity],<>f__AnonymousType34`4[System.String,System.String,System.String,System.String]]>))
)

.Lambda #Lambda1<System.Func`2[DALube_DeacomModel.Dmbill,System.Int32]>(DALube_DeacomModel.Dmbill $bill) {
    $bill.Bi_s1id
}

.Lambda #Lambda2<System.Func`2[DALube_DeacomModel.Dmso1,System.Int32]>(DALube_DeacomModel.Dmso1 $so) {
    $so.S1_id
}

.Lambda #Lambda3<System.Func`3[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1,<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1]]>(
    DALube_DeacomModel.Dmbill $bill,
    DALube_DeacomModel.Dmso1 $so) {
    .New <>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1](
        $bill,
        $so)
}

.Lambda #Lambda4<System.Func`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],System.String]>(<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1] $<>h__TransparentIdentifier6c)
{
    ($<>h__TransparentIdentifier6c.so).S1_name
}

.Lambda #Lambda5<System.Func`2[DALubeBarcodeData.Usersecurity,System.String]>(DALubeBarcodeData.Usersecurity $us) {
    $us.Cparentno
}

.Lambda #Lambda6<System.Func`3[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity,<>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity]]>(
    <>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1] $<>h__TransparentIdentifier6c,
    DALubeBarcodeData.Usersecurity $us) {
    .New <>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity](
        $<>h__TransparentIdentifier6c,
        $us)
}

.Lambda #Lambda7<System.Func`2[<>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity],System.Boolean]>(<>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity] $<>h__TransparentIdentifier6d)
{
    ($<>h__TransparentIdentifier6d.us).Ctype == "JOBSITE" && ($<>h__TransparentIdentifier6d.us).Csiteno != null && ($<>h__TransparentIdentifier6d.us).Csiteno !=
    System.String.Empty && (($<>h__TransparentIdentifier6d.<>h__TransparentIdentifier6c).bill).Bi_smid == .Constant<DALubeBarcode.App_Code.DataAccess+<>c__DisplayClass6e>(DALubeBarcode.App_Code.DataAccess+<>c__DisplayClass6e).iSalesNo
}

.Lambda #Lambda8<System.Func`2[<>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity],<>f__AnonymousType34`4[System.String,System.String,System.String,System.String]]>(<>f__AnonymousType33`2[<>f__AnonymousType32`2[DALube_DeacomModel.Dmbill,DALube_DeacomModel.Dmso1],DALubeBarcodeData.Usersecurity] $<>h__TransparentIdentifier6d)
{
    .New <>f__AnonymousType34`4[System.String,System.String,System.String,System.String](
        ($<>h__TransparentIdentifier6d.us).Ccustno,
        ($<>h__TransparentIdentifier6d.us).Dispname,
        ($<>h__TransparentIdentifier6d.us).Csiteno,
        (($<>h__TransparentIdentifier6d.<>h__TransparentIdentifier6c).so).S1_name)
}
]
   DALubeBarcode.App_Code.DataAccess.GetLocationsBySalesNo(String salesNo) in c:\inetpub\wwwroot\DA_Lube_CLS\DALubeBarcode\App_Code\DataAccess.cs:875
   DALubeBarcode.Controllers.HomeController.FillCustomerLocations() in c:\inetpub\wwwroot\DA_Lube_CLS\DALubeBarcode\Controllers\HomeController.cs:1017
   DALubeBarcode.Controllers.HomeController.CustomerDashboard() in c:\inetpub\wwwroot\DA_Lube_CLS\DALubeBarcode\Controllers\HomeController.cs:982
   lambda_method(Closure , ControllerBase , Object[] ) +101
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +211
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.Async.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() +28
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +48
   System.Web.Mvc.Async.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() +57
   System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223
   System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__36(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +48
   System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20() +24
   System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult) +102
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +43
   System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__18(IAsyncResult asyncResult) +14
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +57
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +47
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__3(IAsyncResult asyncResult) +25
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +47
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9658236
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34280

I tried breaking the query apart and thought it had to do with some of the fields having extra spaces, so I added a Trim() function in the ON portion of the JOIN.  This continued to give me the same error.  I tried finding ways to use the Trim() command using a LET statement, but continued to get errors.  I don't think I need to post all my attempts here, but I can if someone thinks they would help.

So what is wrong with my code and how do I fix it to to (1.) run without errors and (2.) get the same results as running the SQL query in SQL server?

[Doroteya]

Hello Dan,

Let me start by saying that Telerik Data Access is the new name of Telerik OpenAccess ORM. Besides the name, this is one and the same product.

On your first question, the article in the documentation that you found is the approach recommended for executing SQL queries from your code. In your case, you can use the Executing Parameterized SQL Queries approach. As for materializing the result to a list, you could use the ToList() extension method on the result returned by ExecuteQuery<T>(). On the topic of the security, this approach is as safe as the string with the query is. If you expose it somewhere (as in a config file) and somebody finds it, they will be able to abuse it.

Regarding the second question, I tested a LINQ query that is the same in nature as the one you composed and I didn't reproduce the error. This makes me suspect that the cause for it is in the model on your side. Could you send me in a code snippet the Dmbill persistent class and its mapping configuration (from the metadatasource class on your side)?

Additionally, both my suggestion for executing the SQL query and the test I did of your LINQ query are available in the attached sample. It uses the SofiaCarRental sample database and you can find its script here.

I hope this helps.

Regards,
Doroteya
Telerik