2

in my lightswitch application im trying to create a Customers Screen for customers who have balance value, and my balance value is a Calculated Field in the Customer Entity

when i tried to put the logic in the Process Query Event Like This 

query =( from i in query

where(i.Balance>0)
select i );

i get an exception .. what is the best way to handle these kind of situations ?? i saw an answer here but i didn't know how to implement it exactly i need a sample code for it can anyone help me ?? thanks in advance

3oon
  • 365
  • 1
  • 5
  • 15
  • _An exception_. You should know by now that that _does not work_ at Stack Overflow. Nevertheless, as the answer says, it's obvious that you can't use a calculated field this way. What sample code do you want? What did you try yourself so far? – Gert Arnold Mar 01 '12 at 12:20
  • i tried creating a new field on my customer table and i used the Customer validating event to set it's value to equal my Balance calculated field .. but that didn't work as well – 3oon Mar 01 '12 at 13:22

2 Answers2

1

I'm actually looking for a solution to this problem (unfortunately I can't just include the calculation from the calculated field, because my calculation uses another calculated field which uses recursion)

A couple of pointers I thought you might find helpful: @julio.g - for the Executed event handler, the "result" parameter is an IEnumerable parameter (as opposed to the PreProcess_Query event handler, where the "query" parameter is a ref IEnumerable) so any changes you make to the "result" will only be local to that method

@3oon - afaik, SQL Views are not supported in LightSwitch. The best option I've come across so far is to create a WCF RIA Service based on a Stored Procedure, then adding it as a datasource. This blog post should help get you started. http://tejana.wordpress.com/2010/12/09/microsoft-lightswitch-and-stored-procedures-using-wcf-ria-services/

Hope that helps!

Richard Kam
  • 11
  • 1
1

The query will be executed by the data provider, which doesn't know about calculated fields. What you can do is to filter what you want via LINQ, referring to the actual fields, not the calculated ones.

For example, let's say Balance is your calculated field, that you defined as Credit - Debit (which are normal fields). You want your query to return the rows where Balance > 0. This is how you'd write the query (in the PreprocessQuery event, note there is no ProcessQuery event): 

partial void TestQuery_PreprocessQuery(ref IQueryable<Customer> query)
{
    query = (
        from c in query
        where ((c.Credit - c.Debit) > 0)
        select c);
}

Another theoretical way of solving the problem would be setting a filter in the Executed event handler. However, for whatever the reason, when I do it, the filter is not applied to the screen. But even if this method would work, still you'd be filtering on the client side, which might not be what you want.

julio.g
  • 3,268
  • 5
  • 28
  • 25
  • my Credit and Debit values are in different table called "CustomersDebits" not in the customer table itself .. i get the debit value var SumDebit = (from d in CustomerDebits where d.Customer.CustomerId == CustomerId select d.Debit).Sum(); and the same for the credit .. so still i cant do one query to get the required data .. please help – 3oon Mar 03 '12 at 19:15
  • 1
    @3oon: Note that still on the case you describe, you could do it in one query, for example: query = ( from c in query where (((from cr in c.CustomerCredits select cr.Amount).Sum()) - (((from d in c.CustomerDebits select d.Amount).Sum())) > 0) select c); But while still technically possible, I wouldn't do that, performance most certainly is not good. Did you try wrapping your sql in a Sql Server view and then having a Search based on the view? – julio.g Mar 03 '12 at 22:34
  • thats totally answers my question with your linq query, about the view i didn't try to do that i still don't know how lightswitch handles views but i will search for it thanks a lot man :) – 3oon Mar 04 '12 at 06:44
  • what if my comparison value is not fixed (in this case 0) but is a parameter? – Mauro Sampietro Feb 21 '14 at 17:55