none
Need help debugging a query RRS feed

  • Question

  • I'm sorry for asking for help on a query like this but I'm stuck and I would like to know where I've gone wrong

    The error msg I'm getting is:  Exception has been thrown by the target of an invocation.  Member access 'System.DateTime RequiredDate' of 'StockDemand' not legal on type 'System.Linq.IGrouping`2[<>f__AnonymousType22`2[System.Int32,System.Int64],StockDemand].

    When I remove the left join on the demand sub query, the query works fine: 

     

    // on pm.Project_MaterialID equals demand.Key.SourceID into demands
    
    on
    pm.Project_MaterialID equals demand.Key.SourceID

     

    However, I need the left join and I am testing for demand = null in the select statement:

    Thanks for any assistance.

    IQueryable<MaterialShipmentsForecastReportRow> query =
           (from pm in db.Project_Materials
           join projectManager in db.Employees on pm.Project_AVSystem.Project_Header.ProjectManagerID equals projectManager.EmployeeID
           join salesEngineer in db.Employees on pm.Project_AVSystem.Project_Header.SalesPersonID equals salesEngineer.EmployeeID
           join demand in
             (
               from demand in db.StockDemands
               group demand by new
               {
                 WarehouseID = demand.WarehouseID,
                 SourceID = demand.Release_Detail.SourceID,
               } into g
               select g
    
             ) on pm.Project_MaterialID equals demand.Key.SourceID into demands
           from demand in demands.DefaultIfEmpty()
           where (pm.Project_AVSystem.Project_Header.WarehouseID == warehouseID || warehouseID == 0)
           && (projectManager.EmployeeID == projectManagerID || projectManagerID == 0)
           && (salesEngineer.EmployeeID == salesEngineerID || salesEngineerID == 0)
           && (pm.Project_AVSystem.ProjectID == projectID || projectID == 0)
    
           select new MaterialShipmentsForecastReportRow
           {
             ProjectID = pm.Project_AVSystem.ProjectID,
             ProjectNumber = pm.Project_AVSystem.Project_Header.ProjectNumber,
             ProjectName = pm.Project_AVSystem.Project_Header.ProjectName,
             EndUserName = pm.Project_AVSystem.Project_Header.EndUserAccountName,
             WarehouseID = pm.Project_AVSystem.Project_Header.WarehouseID,
             WarehouseName = pm.Project_AVSystem.Project_Header.Warehouse.CompanyName,
             ProjectManager = projectManager.FirstName.Trim() + " " + projectManager.LastName.Trim(),
             SalesEngineer = salesEngineer.FirstName.Trim() + " " + salesEngineer.LastName.Trim(),
             ForecastPastDue = demand == null ? 0 : demand.Where(x => x.RequiredDate < today).Sum(x => (int?)(x.DemandQty - x.QtyShipped) * pm.Price) ?? 0 ,
             //Forecast0To30 = demand == null ? 0 : demand.Where(x => x.RequiredDate >= today && x.RequiredDate <= today.AddDays(30)).Sum(x => (int?)(x.DemandQty - x.QtyShipped) * pm.Price) ?? 0,
             //Forecast31To60 = demand == null ? 0 : demand.Where(x => x.RequiredDate > today.AddDays(30) && x.RequiredDate <= today.AddDays(60)).Sum(x => (int?)(x.DemandQty - x.QtyShipped) * pm.Price) ?? 0,
             //Forecast61To90 = demand == null ? 0 : demand.Where(x => x.RequiredDate > today.AddDays(60) && x.RequiredDate <= today.AddDays(90)).Sum(x => (int?)(x.DemandQty - x.QtyShipped) * pm.Price) ?? 0,
             //ForecastOver90 = demand == null ? 0 : demand.Where(x => x.RequiredDate > today.AddDays(90)).Sum(x => (int?)(x.DemandQty - x.QtyShipped) * pm.Price) ?? 0,
             UnReleased = (pm.Qty - pm.QtyReleased) * pm.Price
           });
    

    Tuesday, August 31, 2010 11:55 PM

All replies

  • Hi Dear Friend

    List<Order> orders = new List<Order>() { new Order(){CustomerID = 0, OrderID = 1,OrderDate = new DateTime(2010,1,1)} , new Order(){CustomerID = 1, OrderID = 2,OrderDate = new DateTime(2011,1,1)} }; var query = from c in customers join o in orders on c.CustomerID equals o.CustomerID select new {FullName = c.Name + " " + c.Family , c.CustomerID , o.OrderDate , TotalOrder = o.Total }; foreach (var item in query) Console.WriteLine(item.FullName + " ,ID= " + item.CustomerID + " ,Order Date= " + item.OrderDate + " ,Total Order="+ item.TotalOrder );

    Sunday, December 25, 2011 1:19 PM