none
how can i filter query using a computed property RRS feed

Answers

  • Hi Ilanp

    Imagine you have an invoice detail table with these fields (Simplified):

    Id

    Product

    Quantity

    Price

    Subtotal (computed field) result = Quantity * Price

     

    And you need to filter records with over 100 Subtotal.

    Then on the detail table to create a query which may include a parameter:

    P_Subtotal, Money Type

    and the method Preprocessquery write the following code:

    IsNot Nothing Then If P_Subtotal

          Query = query.where (Function (a) (a. Quantity * a.Price)> P_Subtotal)

    End If

    Now create a query EditableGrid on. F5, now in the control parameter can put any value you want to filter, then tab and get the filtered list.


    Jaime If you found this post helpful, please: VoteAsHelpful. If it answered your question, remember to: MarkAsAnswer.
    • Edited by JaimeH Saturday, September 10, 2011 8:34 PM
    • Marked as answer by pint12 Sunday, September 11, 2011 6:38 AM
    Saturday, September 10, 2011 8:33 PM
    1. Edit the query in the query designer (by dbl-clicking the query name in solution explorer).
    2. Click the "Write Code" dropdown
    3. Select "TheNameOfYourQuery_PreProcessQuery" in the list
    4. You'll be taken to the correct place, & the method stub will be written for you.

    Yann

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    • Marked as answer by pint12 Sunday, September 11, 2011 6:38 AM
    Sunday, September 11, 2011 6:21 AM
    Moderator
  • If you qurey need to return just one row from entity by key (e.g. you need to find Customer by ID). In that case you use SingleOrDefault.

    But, if your query return many rows, use ALL, because you have criteria that return many rows from entity and you want them all (e.g. search entity by part of the name or so. Let say Return All Customers).

    Hope this help

    Best regards

    Spaso Lazarevic


    • Edited by Spaso Lazarevic Sunday, September 11, 2011 7:30 AM
    • Proposed as answer by Spaso Lazarevic Sunday, September 11, 2011 7:30 AM
    • Marked as answer by pint12 Sunday, September 11, 2011 7:31 AM
    Sunday, September 11, 2011 7:24 AM
  • Yes, it's lambda syntax.

    query = query.where ((a) => (a. Quantity * a.Price)> P_Subtotal);
    


    Yann

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    • Marked as answer by pint12 Sunday, September 11, 2011 9:15 AM
    Sunday, September 11, 2011 9:12 AM
    Moderator

All replies

  • The point is that you can't use computed property for filter because in filter you can use only properties from entity.

    Spaso Lazarevic

    • Proposed as answer by JaimeH Friday, September 9, 2011 1:02 PM
    Friday, September 9, 2011 12:49 PM
  • and how and where do i do that? 

     


    ilanp
    Friday, September 9, 2011 12:59 PM
  • Hi Ilanp

    If you need to order a computed field must do so by the operation that defines its content, for example:

    Computed field:

    Result = FirstName + " " + LastName

    query with order:

    query = query.OrderBy(Function(a) a.FirstName + " " + a.LastName)


    Jaime If you found this post helpful, please: VoteAsHelpful. If it answered your question, remember to: MarkAsAnswer.
    Friday, September 9, 2011 1:07 PM
  • And Ilanp

    If you use Jaime's query in PreprocessQuery you'll get order wherever that entity is used.

    Best regards

    Friday, September 9, 2011 1:19 PM
  • If your computed fields are that simple as FirstName + LastName, then Jaime's idea will work.  But if your filed has some runtime calculation or similar to that, then you are out of luck.  Computed fields can't be filtered or sorted.  The only workaround is, create a real field in the entity for the computed field. This is what I'm doing on all my entities.  Annoying, but no way around.

     

    I'm not sure of this, but have you tried LS' filter extension?  I know it supports foreign key filters, but not sure about computed filed.  Again even if it works, I don't think thats what you want it.


    -Bala
    Friday, September 9, 2011 1:39 PM
  • ///i have created a computed property  for my order table - bool "show"///

     

    Why didn't get this first. :( :(


    -Bala
    Friday, September 9, 2011 1:42 PM
  • hi jaimeh 

    tnx for your answer. 

    but  i need 2 more helps 

    1. where and how i create the Function(a)

    2. can use the  query.select ?? or where ?

     


    ilanp
    Saturday, September 10, 2011 7:15 PM
  • Hi Ilanp

    Imagine you have an invoice detail table with these fields (Simplified):

    Id

    Product

    Quantity

    Price

    Subtotal (computed field) result = Quantity * Price

     

    And you need to filter records with over 100 Subtotal.

    Then on the detail table to create a query which may include a parameter:

    P_Subtotal, Money Type

    and the method Preprocessquery write the following code:

    IsNot Nothing Then If P_Subtotal

          Query = query.where (Function (a) (a. Quantity * a.Price)> P_Subtotal)

    End If

    Now create a query EditableGrid on. F5, now in the control parameter can put any value you want to filter, then tab and get the filtered list.


    Jaime If you found this post helpful, please: VoteAsHelpful. If it answered your question, remember to: MarkAsAnswer.
    • Edited by JaimeH Saturday, September 10, 2011 8:34 PM
    • Marked as answer by pint12 Sunday, September 11, 2011 6:38 AM
    Saturday, September 10, 2011 8:33 PM
  • ok i got it better now 

    one last thing 

    where do i add the 

    method Preprocessquery ( write code-> active?)  ? 

    could you give me a syntax exmple plz ?  


    ilanp
    Sunday, September 11, 2011 5:44 AM
    1. Edit the query in the query designer (by dbl-clicking the query name in solution explorer).
    2. Click the "Write Code" dropdown
    3. Select "TheNameOfYourQuery_PreProcessQuery" in the list
    4. You'll be taken to the correct place, & the method stub will be written for you.

    Yann

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    • Marked as answer by pint12 Sunday, September 11, 2011 6:38 AM
    Sunday, September 11, 2011 6:21 AM
    Moderator
  • "singleORdefult" OR "ALL"? 
    ilanp
    Sunday, September 11, 2011 7:11 AM
  • If you qurey need to return just one row from entity by key (e.g. you need to find Customer by ID). In that case you use SingleOrDefault.

    But, if your query return many rows, use ALL, because you have criteria that return many rows from entity and you want them all (e.g. search entity by part of the name or so. Let say Return All Customers).

    Hope this help

    Best regards

    Spaso Lazarevic


    • Edited by Spaso Lazarevic Sunday, September 11, 2011 7:30 AM
    • Proposed as answer by Spaso Lazarevic Sunday, September 11, 2011 7:30 AM
    • Marked as answer by pint12 Sunday, September 11, 2011 7:31 AM
    Sunday, September 11, 2011 7:24 AM
  • i get a intelisens  msg 

    Function (a) does not exists 

    help ? 

    where should i create the Function (a) ?


    ilanp
    Sunday, September 11, 2011 7:38 AM
  • If you're using C#, then this is th wrong syntax, it's VB syntax.

    Replace "Function(a)" with "a =>"


    Yann

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Sunday, September 11, 2011 9:02 AM
    Moderator
  • i am . 

    lambada expression ? 

     


    ilanp
    Sunday, September 11, 2011 9:05 AM
  • Yes, it's lambda syntax.

    query = query.where ((a) => (a. Quantity * a.Price)> P_Subtotal);
    


    Yann

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    • Marked as answer by pint12 Sunday, September 11, 2011 9:15 AM
    Sunday, September 11, 2011 9:12 AM
    Moderator
  • i would like  to thank you all!!! 

    have a nice day 


    ilanp
    Sunday, September 11, 2011 9:16 AM
  • how can i write the 

    "IsNot Nothing Then If P_Subtotal" part with c#? 



    ilanp
    Sunday, September 11, 2011 7:28 PM
  • You can use DeveloperFusion to convert code.

    But in your case

     

    if (Quantity != null & Price != null)
    {
    	query = query.Where(a => (a.Quantity * a.Price) > P_Subtotal)
    }
    

    Hope this help

     

    Spaso Lazarevic




    Sunday, September 11, 2011 7:40 PM
  • ok i have a problem 

     

    this is my code 

     

            partial void Nop_Orders_All_PreprocessQuery(ref IQueryable<Nop_Order> query)

            {

     

                query = query.Where(a => a.show > 0 ); 

            }

     

     

    1. i cant use the computed parmteter at the if . it doset recognized there /

    2. without the if  i get an run time err   Expression is not supported. Expression

    :

     

     

    {Microsoft.LightSwitch.DataServiceOperationException: הביטוי אינו נתמך. ביטוי: LightSwitchApplication.Nop_Order[].Where(a => (a.show > 0))

    הודעת חריגה פנימית:

    הביטוי אינו נתמך. ביטוי: a => (a.show > 0)

    הודעת חריגה פנימית:

    הביטוי אינו נתמך. ביטוי: a => (a.show > 0)

    הודעת חריגה פנימית:

    הביטוי אינו נתמך. ביטוי: (a.show > 0)

    הודעת חריגה פנימית:

    הביטוי אינו נתמך. ביטוי: a.show

    הודעת חריגה פנימית:

    החבר 'show' בסוג 'LightSwitchApplication.Nop_Order' אינו נתמך.

       ב Microsoft.LightSwitch.Framework.Base.ExecutableObject.Execute(Boolean allowJoin)

       ב Microsoft.LightSwitch.Details.Framework.Client.ScreenPropertyLoader`1.LoadIfNecessary()

       ב Microsoft.LightSwitch.Details.Framework.Client.ScreenDetails`2.GetValue[TValue](Entry entry, Data& data)

       ב Microsoft.LightSwitch.Details.Framework.Client.ScreenDetails`2.GetValue[T](Entry entry, Data& data)

       ב Microsoft.LightSwitch.Details.Framework.Client.ScreenCollectionProperty`3.Entry.<.ctor>b__14(TDetails details, Data& data)

       ב Microsoft.LightSwitch.Details.Framework.Internal.DetailsDelegate.FuncStackFrame`3.Invoke(TDetails details, TData& data)

       ב Microsoft.LightSwitch.Details.Framework.Internal.DetailsDelegate.FuncStackFrame`3.Callback(TDetails details, TData& data, Object stackFrame)

       ב LightSwitchApplication.SearchNop_Orders.DetailsClass.PropertySetProperties._Nop_Orders_Stub(DetailsCallback`2 c, DetailsClass d, Object sf)

       ב Microsoft.LightSwitch.Details.Framework.Internal.DetailsDelegate.FuncStackFrame`3.InvokeVia(DetailsStub`2 stub, DetailsFunc`3 target, TDetails details)

       ב Microsoft.LightSwitch.Details.Framework.Internal.DetailsDelegate.InvokeVia[TDetails,TData,TResult](DetailsFunc`3 target, TDetails details, DetailsStub`2 stub)

       ב Microsoft.LightSwitch.Details.Framework.Client.ScreenCollectionProperty`3.Entry.<.ctor>b__8(TDetails d)

       ב Microsoft.LightSwitch.Details.Framework.Client.ScreenDetails`2.GetPropertyValue[TValue](TScreen screen, Entry entry)

       ב Microsoft.LightSwitch.Details.Framework.Client.ScreenDetails`2.GetValue[T](TScreen screen, Entry entry)

       ב LightSwitchApplication.SearchNop_Orders.get_Nop_Orders()}

     

     


    ilanp
    • Edited by pint12 Sunday, September 11, 2011 7:57 PM
    Sunday, September 11, 2011 7:51 PM
  • Maybe you can try without if clause in your PreprocessQuery method.

    Just use

    query = query.Where(a => (a.Quantity * a.Price) > P_Subtotal)
    

    and see do you have any errors

    Spaso

    Sunday, September 11, 2011 8:18 PM
  • this is what i did in first place ! seems that there is problem with parmeters 
    ilanp
    Sunday, September 11, 2011 8:42 PM
  • Please give me you whole method.

    You need to have properties in your Entity:

    Quantity

    Price

    and P_Subtotal as computed property

    Spaso

    Sunday, September 11, 2011 8:45 PM
  • computed property method  :
    public partial class Nop_Order
        {
            partial void show_Compute(ref int result)
            {
                // Set result to the desired field value
                var t = from order in Nop_OrderProductVariants
                        where order.warehouse == Application.User.Name
                        select order;
                int count = t.Count(); 
                if (count<1) 
                { 
                    result = 1 ; //true
                } 
                else
                { result = 0; }  //false
                
               
            }
    PreprocessQuery:

       partial void Nop_Orders_All_PreprocessQuery(ref IQueryable<Nop_Order> query)

            {

     

                query = query.Where(a => a.show > 0 ); 

            }


    ilanp
    Sunday, September 11, 2011 8:56 PM
  • Spaso's original reply stated "The point is that you can't use computed property for filter because in filter you can use only properties from entity".

    The problem is you're trying to filter by a computed property.

    EF, & therefore LS, doesn't "understand" your computed property, becuase it only exists as an LS concept, it doesn't exist in the database.


    Yann

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Monday, September 12, 2011 12:21 AM
    Moderator
  • well yann 

    i disagree with you  couse it seems that the guy in the folowing post: 

    http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/d2e04e57-b1e0-438e-9960-6551c144e886

    found an answer.

    / in my code i am missing this part : 

    Public Shared ReadOnly Property IsDeliveryIncompleteExpression As Expression(Of Func(Of PurchaseOrder, Boolean))
     Get
      ' Returns an expression that determins if there are detail items that have a lacking greater than 0
      Return Function(order As PurchaseOrder) order.PurchaseOrderDetails.Any(Function(detail) detail.Lackings > 0)
     End Get
    End Property
    
    Private Shared ReadOnly Property IsDeliveryIncomplete As Func(Of PurchaseOrder, Boolean)
     Get
      Return PurchaseOrder.IsDeliveryIncompleteExpression.Compile()
     End Get
    End Property



     
    how and where  i  can implement it with c# ? 


    ilanp
    Monday, September 12, 2011 6:18 AM
  • Well, actually, no, the two people in that post agree with what I said.

    RKage: "I find that there is no way we can define a query using a computed property of an entity" & "Unfortunately, computed property can not be use in defining a query"

    Justin: "You cannot use computed properties as a part of a query because computed properties to not actually exist in the database where the query will be serialized and submitted"

    So what I stated was correct, & is exactly what they're both saying.

    Justin then goes on to say "To get around this problem, you can refactor your computed property code into an expression that is serializable by RIA and use the expression both in your computed property and in the PreprocessQuery method of the query".

    That's *not* filtering using a computed property, that's a workaround that uses the same "logic" that your computed property was using. And I don't believe that will *always* be possible either.

    I'm glad there's a "workaround" that might help you do what you want, but my statement agreeing with Spaso's original post "you can't use computed property for filter because in filter you can use only properties from entity" still stands.

    The C# equivalent of that code is:

    public static Expression<Func<PurchaseOrder, bool>> IsDeliveryIncompleteExpression
    {
        get
        {
            // Returns an expression that determins if there are detail items that have a lacking greater than 0
            return (PurchaseOrder order) => order.PurchaseOrderDetails.Any((detail) => detail.Lackings > 0);
        }
    }
    
    private static Func<PurchaseOrder, bool> IsDeliveryIncomplete
    {
        get
        {
            return PurchaseOrder.IsDeliveryIncompleteExpression.Compile();
        }
    }
    


    Yann

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Monday, September 12, 2011 8:29 AM
    Moderator
  • Hi to all

    As title of this post is "how can i filter query using a computed property" I think that we agree that there are a lot of answers in this post that point to fact that we can't use computed property in filter as Ilanp want to use. So everything else is another subject. Thanks

    Spaso Lazarevic

    Monday, September 12, 2011 8:45 AM