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

2 posts, 0 answers
  1. Dan
    Dan avatar
    7 posts
    Member since:
    Jul 2011

    Posted 19 Feb Link to this post

    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?

  2. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 24 Feb Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
Back to Top