none
prices history per product query RRS feed

  • Question

  • here the scenario that i am facing :

    suppose that i have a typical Customers/orders/orderdetails/products/ tables relationship .

    when i select an order , i want to display orderdetails of that order, but also another column for the previous price of the product and for the same customer

    Product               Orderdetails_Price            Previous_price

    580GTX                    2000$                                  2200$
    intelI7                        500$                                    480$
    SSD                          1000$                                  1500$

    so for each order details product i want to know the price it was sold in the previous customer order based on date

    i have tried something like :

    DBEntities   context = new DBEntities();

     
     var DataSource = from o in  context.ORDERDETAILs.Where(a => a.ORDERID == 2)   /* 2 is the selected order ID */
                                 select new  
                                 {
                                    PRODUCT = o.PRODUCT.NAME,
                                     PRICE = o.ORDERPRICE,
                                     PREVIOUSPRICE=context.ORDERDETAILs.FirstOrDefault(b=>
                                     b.CUSTOMER.CUSTOMERID  ==  o.CUSTOMER.CUSTOMERID
    
                                     &&
                                    b.PRODUCTID == o.PRODUCTID
                                     &&
                                    b.ORDER.ORDERDATE< o.ORDER.ORDERDATE).ORDERPRICE
                                 };


    it gave me an error complaining about server not implemented that or something ,

    also i am missing the orderby date to get the first previous price based on date ,

    i didn't figure out how to use it .

    so what i am triyng to do

    is for a given order, select the orderdetails associated with, and for each product get the last price sold with for that customer

    any help is welcome

    thanks and good day

    • Edited by issam1975 Monday, February 16, 2015 8:14 AM
    Monday, February 16, 2015 8:13 AM

Answers

  • Hello,

    >>and it gave me the same error

    From exception, I infer that you should have a data binding behavior as:

    this.GridView1.DataSource = DataSource;

    If so, the caused reason for this exception actually already shows us, we cannot binding a Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery, DbRawSqlQuery). As it suggests, for WPF bind to DbSet.Local, for WinForms bind to DbSet.Local.ToBindingList(), for ASP.NET WebForms you can bind to the result of calling ToList(), I assume that your application is a web app, so you could write query as below:

    var DataSource = (from o in context.Order_Details.Where(a => a.OrderID == 11077)   
    
                                 select new
    
                                 {
    
                                     PRODUCT = o.Product.ProductID,
    
                                     PRICE = o.UnitPrice,
    
                                     PREVIOUSPRICE = context.Order_Details.FirstOrDefault(b =>
    
                                     b.Order.CustomerID == o.Order.CustomerID
    
                                     &&
    
                                    b.ProductID == o.ProductID
    
                                     &&
    
                                    b.Order.OrderDate < o.Order.OrderDate).UnitPrice
    
                                 }).ToList();

    Or

    this.GridView1.DataSource = DataSource.ToList();

    If I do not understand correctly, please feel free to let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, February 18, 2015 6:44 AM
    Moderator

All replies

  • Hello issam1975,

    >>here the scenario that i am facing : suppose that i have a typical Customers/orders/orderdetails/products/ tables relationship

    According to your description and provided LINQ query, I am a bit confused because according to your linq query, I notice that you use “PRODUCT = o.PRODUCT.NAME,” to get the product name, for this, the relationship should be:

    Customers(1-*)orders(1-*)orderdetails(1-1)products

    While from your last statement “is for a given order, select the orderdetails associated with, and for each product get the last price sold with for that customer “, it seems that these relationship between orderdetails and products is 1-*, I suggest that you could provide the exact table schema, and provide the columns you needed so that we can construct a linq query according that.

    >> it gave me an error complaining about server not implemented that or something ,

    If possible, please share the exception with us so that we can understand what happens for your current linq query.

    >> also i am missing the orderby date to get the first previous price based on date , i didn't figure out how to use it .

    In LINQ query, you could use the OrderBy syntax for filter data in order, for details, you could check this link:

    https://msdn.microsoft.com/en-us/library/vstudio/bb399396(v=vs.100).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 17, 2015 2:24 AM
    Moderator
  • Hi Fred !


    My bad !, i have used this typical schema just to showcase the issue .

    While from your last statement “is for a given order, select the orderdetails associated
    with, and for each product get the last price sold with for that customer


    this is exactly what i am trying to do, just little confused on how it can be done .

    now with northwind database and EF6, here what i have tried again

       NorthwindEntities context = new NorthwindEntities();
                   
    
                var DataSource = from o in context.Order_Details.Where(a => a.OrderID == 11077)   
    /* 11077 is the selected order ID */
                                 select new
                                 {
                                     PRODUCT = o.Product.ProductID,
                                     PRICE = o.UnitPrice,
                                     PREVIOUSPRICE = context.Order_Details.FirstOrDefault(b =>
                                     b.Order.CustomerID == o.Order.CustomerID
    
                                     &&
                                    b.ProductID == o.ProductID
                                     &&
                                    b.Order.OrderDate < o.Order.OrderDate).UnitPrice
                                 };

    and it gave me the same error



    Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery, DbRawSqlQuery)
    is not supported. Instead populate a DbSet with data, for example by calling Load
    on the DbSet, and then bind to local data. For WPF bind to DbSet.Local.
    For WinForms bind to DbSet.Local.ToBindingList().
    For ASP.NET WebForms you can bind to the result of calling ToList() on
    the query or use Model Binding,
    for more information see http://go.microsoft.com/fwlink/?LinkId=389592.

    really appreciate your help on this








    • Edited by issam1975 Tuesday, February 17, 2015 1:33 PM
    Tuesday, February 17, 2015 1:27 PM
  • Hello,

    >>and it gave me the same error

    From exception, I infer that you should have a data binding behavior as:

    this.GridView1.DataSource = DataSource;

    If so, the caused reason for this exception actually already shows us, we cannot binding a Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery, DbRawSqlQuery). As it suggests, for WPF bind to DbSet.Local, for WinForms bind to DbSet.Local.ToBindingList(), for ASP.NET WebForms you can bind to the result of calling ToList(), I assume that your application is a web app, so you could write query as below:

    var DataSource = (from o in context.Order_Details.Where(a => a.OrderID == 11077)   
    
                                 select new
    
                                 {
    
                                     PRODUCT = o.Product.ProductID,
    
                                     PRICE = o.UnitPrice,
    
                                     PREVIOUSPRICE = context.Order_Details.FirstOrDefault(b =>
    
                                     b.Order.CustomerID == o.Order.CustomerID
    
                                     &&
    
                                    b.ProductID == o.ProductID
    
                                     &&
    
                                    b.Order.OrderDate < o.Order.OrderDate).UnitPrice
    
                                 }).ToList();

    Or

    this.GridView1.DataSource = DataSource.ToList();

    If I do not understand correctly, please feel free to let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, February 18, 2015 6:44 AM
    Moderator