none
ExecuteQuery(Of table) how to access the computed column after datacontext.ExecuteQuery(Of table) RRS feed

  • Question

  • target = db.ExecuteQuery(Of priceTable)("SELECT Proudct, Price, price * 0.10 as commission from priceTable order by commission" )

    The query works fine without errors but I like to access the commission field.

    for each rec in target
    product = rec.product
    comm =  rec.commissin ??????
    This does not work as the field is not the part of the table but calculated in the query.

    • Edited by ToThePoint Sunday, June 28, 2009 2:04 AM
    Sunday, June 28, 2009 1:03 AM

All replies

  • Hi,

    How about something like:

    var prices = db.ExecuteQuery(Of PriceTable) ( "Select Product, Price from PriceTable order by Commission" );
    var target =
       from ProductPrice P in prices _
       select New ProductCommision with (.Product=P.Product, .Price=P.Price, .Commission=P.Price*0.10 )


    However, I would prefer it using standard LINQ (without executequery), something like:
    var target =
       from ProductPrice P in db.PriceTable_
       select New ProductCommision with (.Product=P.Product, .Price=P.Price, .Commission=P.Price*0.10 )
    


    Hope that helps.

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    Sunday, June 28, 2009 8:05 AM
  • I have same pricetable with different names for each country. So I have to change the table name at the run time depending on the country. How can I do this thru standard linq?

    Sunday, June 28, 2009 12:07 PM
  • Then you may use the first sample from my previous post.

    var prices = db.ExecuteQuery(Of PriceTable) ( "Select Product, Price from " & PriceTableName & " order by Commission" );
    var target =
       from ProductPrice P in prices _
       select New ProductCommision with (.Product=P.Product, .Price=P.Price, .Commission=P.Price*0.10 )
    

    Notice that all your tables may need to have the same structure in order for this to work.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Sunday, June 28, 2009 2:56 PM
  • Thanks for the help so far. I need one more advice.

    Please see the "SORT" parameter in your query, I musy sort by Commission (calculated field). You are sorting in the first part of the query by commission but the commission is not the part of the table. Please help one more time. 

    Sunday, June 28, 2009 10:31 PM
  • My apologies, I did not notice that. Then you should use:
    var prices = db.ExecuteQuery(Of PriceTable) ( "Select Product, Price from "  & PriceTableName & " order by Price*0.01")
    
    

    Alternatively, you can sort the result in your linq expression as:
    var target =   from ProductPrice P in prices _
       order by P.Price*0.10 _
       select New ProductCommision with (.Product=P.Product, .Price=P.Price, .Commission=P.Price*0.10 )
    
    

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Monday, June 29, 2009 6:47 AM