0

I'm using a MVC project with a Telerik Open Access data project. I haven't had any problems writing linq statements to query the data until now, but I just can't get the linq statement right. Can someone help me translate this SQL code to linq?

Here's my SQL statement. It runs fine in SQL Management Studio and returns 9 records.

SELECT 
  us.ccustno
, so.s1_name
, so.s1_id
FROM DALubeNew.dbo.usersecurity us
INNER JOIN DALubeDeacom.dbo.dmso1 so
    ON us.cparentno = so.s1_name
WHERE us.ccustno = 'BIZ017'

I translated into linq in this method:

public List<DataModelSample> GetLocationsByCustID(string custID)
{
    List<DataModelSample> locations =
        (
            from us in dbContext.Usersecurities
            join so in deacom.Dmso1
                on us.cparentno equals so.S1_name
            where us.Ccustno == custID
            select new DataModelSample
            {
                customerID = us.Ccustno,
                origLocationName = so.S1_name,
                origLocationID = so.S1_id.ToString()
            }
        ).ToList();

    return locations;

  } // GetLocationsByCustID

    EntitiesModel dbContext = new EntitiesModel();
    EntitiesModel_Deacom deacom = new EntitiesModel_Deacom();

As far as I can tell there's nothing unusual about this query. I've queried these tables before without any problem. I wrote linq queries to query both of the tables (Usersecurities and Dmso1) separately and have no problems. This query however, gives me the error "Object reference not set to an instance of an object." Can anyone tell me what's wrong?

I'm open to not using linq too if there's a way to do that. Thank you!

Here is the stack trace if that helps:

> [NullReferenceException: Object reference not set to an instance of an object.]
   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<DALubeBarcodeData.Usersecurity>().Join(Extent<DALube_DeacomModel.Dmso1>(), us => us.Ccustno, so => so.S1_name, (us, so) => new <>f__AnonymousType2d`2(us = us, so = so)).Where(<>h__TransparentIdentifier56 => (<>h__TransparentIdentifier56.us.Ccustno == value(DALubeBarcode.App_Code.DataAccess+<>c__DisplayClass57).custID)).Select(<>h__TransparentIdentifier56 => new DataModelSample() {customerID = <>h__TransparentIdentifier56.us.Ccustno, origLocationName = <>h__TransparentIdentifier56.so.S1_name, origLocationID = <>h__TransparentIdentifier56.so.S1_id.ToString()})'. Failure: Object reference not set to an instance of an object.
See InnerException for more details.
Complete Expression:
.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Where(
        .Call System.Linq.Queryable.Join(
.Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[DALubeBarcodeData.Usersecurity]>(Extent<DALubeBarcodeData.Usersecurity>()),
.Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[DALube_DeacomModel.Dmso1]>(Extent<DALube_DeacomModel.Dmso1>()),
            '(.Lambda #Lambda1<System.Func`2[DALubeBarcodeData.Usersecurity,System.String]>),
            '(.Lambda #Lambda2<System.Func`2[DALube_DeacomModel.Dmso1,System.String]>),
            '(.Lambda #Lambda3<System.Func`3[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1,<>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1]]>))
        ,
        '(.Lambda #Lambda4<System.Func`2[<>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1],System.Boolean]>))
    ,
    '(.Lambda #Lambda5<System.Func`2[<>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1],DALubeBarcodeDataModel.Models.DataModelSample]>))
.Lambda #Lambda1<System.Func`2[DALubeBarcodeData.Usersecurity,System.String]>(DALubeBarcodeData.Usersecurity $us) {
    $us.Ccustno
}
.Lambda #Lambda2<System.Func`2[DALube_DeacomModel.Dmso1,System.String]>(DALube_DeacomModel.Dmso1 $so) {
    $so.S1_name
}
.Lambda #Lambda3<System.Func`3[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1,<>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1]]>(
    DALubeBarcodeData.Usersecurity $us,
    DALube_DeacomModel.Dmso1 $so) {
    .New <>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1](
        $us,
        $so)
}
.Lambda #Lambda4<System.Func`2[<>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1],System.Boolean]>(<>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1] $<>h__TransparentIdentifier56)
{
    ($<>h__TransparentIdentifier56.us).Ccustno == .Constant<DALubeBarcode.App_Code.DataAccess+<>c__DisplayClass57>(DALubeBarcode.App_Code.DataAccess+<>c__DisplayClass57).custID
}
.Lambda #Lambda5<System.Func`2[<>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1],DALubeBarcodeDataModel.Models.DataModelSample]>(<>f__AnonymousType2d`2[DALubeBarcodeData.Usersecurity,DALube_DeacomModel.Dmso1] $<>h__TransparentIdentifier56)
{
    .New DALubeBarcodeDataModel.Models.DataModelSample(){
        customerID = ($<>h__TransparentIdentifier56.us).Ccustno,
        origLocationName = ($<>h__TransparentIdentifier56.so).S1_name,
        origLocationID = .Call (($<>h__TransparentIdentifier56.so).S1_id).ToString()
    }
}
]
   Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid) +553
   Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQueryMulti(Expression expr, ExecutionSettings settings, Object[] grpVals, Boolean checkOid, QueryOptions options) +185
   Telerik.OpenAccess.Query.Piece`1.ExecuteMultiple() +416
 Telerik.OpenAccess.Query.Piece`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +36
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +369
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   DALubeBarcode.App_Code.DataAccess.GetLocationsByCustID(String custID) in c:\inetpub\wwwroot\DA_Lube_CLS\DALubeBarcode\App_Code\DataAccess.cs:687
   DALubeBarcode.Controllers.HomeController.CustomerDashboard() in c:\inetpub\wwwroot\DA_Lube_CLS\DALubeBarcode\Controllers\HomeController.cs:935
   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
boilers222
  • 1,901
  • 7
  • 33
  • 71
  • 1
    Entity Framework doesn't know how to create a DataModelSample object in C#. One way to do this would be to just do select new instead of select new DataModelSample. Afterwards create the DataModelSample object from the anoymous type you created. – wentimo Dec 23 '15 at 21:32
  • Thanks for the reply. First of all, this is Telerik's Open Access data package, not Entity Framework. Second, I've tried just returning one field (like Ccustno) instead of a new DataModelSample and I get the same error. Any other suggestions? – boilers222 Jan 05 '16 at 19:20
  • Try this: Change select new DataModelSample into select new. After the ToList() call do this: var locationsAsClass = locations.Select(x => new DataModelSample(x.Ccustno, x.so.S1_name, so.S1_id)).ToList(); and then return on locationsAsClass. If that doesn't work - what line does it error on? – wentimo Jan 05 '16 at 20:33

0 Answers0