none
Need help understanding ".Any" Operator - "If Exists" RRS feed

  • Question

  • Trying to create a single LINQ statement to mimic the SQL statement I already have in my VB 2008 App.   My biggest hurdle is the the "If exists" statement which I have read is ".Any" in LINQ and I can get a simple ".Any" LINQ statement to run, but just not how I want it to work in my SQL Statement...

    Simplified SQL code (This is SQL code not VB.net code.)

    If Exists(Does this record exist)
    
       it does - then run this Select statement
    
    else
    
       it doesn't - Then run this Select statement 
    
    

    or

    Wrote a simple Example From Northwind

    if Exists(select * from orders where OrderID = 10821)
    
     select CustomerID, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode,ShipCountry
    
     From Orders 
    
     where OrderID = 10821
    
    else
    
     select CustomerID, CompanyName, Address, City, Region, PostalCode, Country
    
     From Customers
    
     Where phone = '(307) 555-4680'
    
    

    I want it in one statement because it is accessed over a VPN from a distance so multiple calls to the server take a while... trying to reproduce in LINQ... I got my True, False LINQ statements to work but trying to understand how to use in conjunction with the ".Any" operator.

    Thanks for any help or advice...

    dotnetme

    Wednesday, May 19, 2010 4:13 PM

All replies

  • The thing is .Any return true if the condition is true to at least one element in the collection so of course you could run the any query, store the value and then use if to select the appropiated records so all in all it will be two queries and you don't seem to want that.  You could make an store procedure or a function but that would defeat the purpose of using LINQ.  Sorry for not being too helpful but I checked some stuff and I don't see an easy way to do this.
    Wednesday, May 19, 2010 5:28 PM
  • IEnumerable query =
    Orders.Any (o => o.OrderID == 10821) ?
    		(
    		from o in Orders
    		where o.OrderID == 10821
    		select new
    		{
    			o.CustomerID,
    			o.ShipName,
    			o.ShipAddress,
    			o.ShipCity,
    			o.ShipRegion,
    			o.ShipPostalCode,
    			o.ShipCountry
    		}
    		) as IEnumerable
    		:
    		(
    		from c in Customers
    		where c.Phone == "(307) 555-4680"
    		select new
    		{
    			c.CustomerID,
    			c.CompanyName,
    			c.Address,
    			c.City,
    			c.Region,
    			c.PostalCode,
    			c.Country
    		}
    		) as IEnumerable
    		;
    Wednesday, May 19, 2010 5:53 PM
  • That is really nice but I'm pretty sure that there are two queries there.  The ?: operator first evaluates the condition (first query) and then executes the first expresion if true and the second if false, basically the same as an if else block but both expresions must return a value and that it has worst readability than if else blocks and is more compact.

     

    Wednesday, May 19, 2010 7:58 PM
  • First thanks to Chris for the response... As for Serguey I'm not sure if it is 2 sql statements or not, but My original statement is two sql statements but the evaluating the statements took place on the Server as the entire "If"statement was sent to SQL Server and the result true or false was sent back. Even though Chris's code may be 2 statements is it converted to sql and sent to SQL Server all at once? Or is it going to send the first part to evaluate to the server and wait for a result before moving on. Trying to covert his example to VB now?

    This is more a desire to use new technology verse a need!

    Thanks to you both for helping me so far

    dotnetme

    Thursday, May 20, 2010 1:44 PM
  • To see if there are two queries or only one you should use the log and trace on the server side.  To be honest I haven't had the time to check (really busy) but the logic dictates that VS check the condition first and then execute one of the block of code so two queries (the compiler whould need an extra logic to check that in the event that inside the block of a control estructure a query was build then the whole statement would be a query).  Check first or wait until I have time on my hands to do it.  However and this is not nice programming, you could make it so in worse case do two queries.

    In code:

     

    var collect=from o in Orders
    where o.OrderID == 10821
    select new
    {
       o.CustomerID,
       o.ShipName,
       o.ShipAddress,
       o.ShipCity,
       o.ShipRegion,
       o.ShipPostalCode,
       o.ShipCountry
    }
    if(collect.Any())
    {
       collect = from c in Customer
       where c.Phone == "(307) 555-4680"
       select new
       {
           c.CustomerID,
           c.CompanyName,
           c.Address,
           c.City,
           c.Region,
           c.PostalCode,
           c.Country
       }
    }
    Thursday, May 20, 2010 6:25 PM
  • By the way I assumed that the issue is the amount of times the data is queried from the server.  Now, LINQ automatically keep a cache of the data so when you do multiples queries you call the database once unless you don't want the default behavior (it can be changed and if you are going to have concurrency issues I think is better to do so).  The ammount of time the connection stay open is as long as it takes for the query to be consumed, if you want it to be smaller you can use ToList().  To see how LINQ translates the query into statements you either use LINQ To DataSet or LINQ To SQL (I don't know how can this be done easily in LINQ To Entities) Log() and you can trace on the server side with the SQL Profiler to see the calls to the database.

    Hope this help

    For more information consult the MSDN

    On a different note I have been testing LINQ To Entities and some of the extension method don't work correctly or at all, perhaps they are not supposed to?

    Friday, May 21, 2010 12:23 PM
  • There is a need to use the data over possibly, depends on the users needs. The actual query takes milliseconds when in the building... but it is the lang due to distance of some users is the issue so I wanted to do it all in one shot... Multiple calls to the server would be an issue. Hard to test the lag at the moment but working on that, going to create some test scenarios without side people to see what I'm dealing with... I created multiple treads to make several calls at same time that are not dependent on one another so it does not lock up application... I may have to stick with sql code for now to may dismay!!! but will use LINQ with other apps now. I really appreciate your help with this.

    Thanks

    dotnetme

    Friday, May 21, 2010 2:42 PM
  • To improve it a bit you could manage the Concurrence so that only connect to db when info has change and get only the part that has.  However if speed is a must I agree that you should stick wiht SQL because LINQ create another abstraction layer and that has its benefits but come with a price.

    Hope this help

    PS: When you get the result from your lag test, could you post a comparison LINQ vs SQL.

    Monday, May 24, 2010 5:41 PM
  • If single roundtrip is the goal, you can always do a union, e.g. something like this:

    int orderID = 10821;
    string phoneNo = "(307) 555 4680";

    var query = (
      from o in dc.Orders
      where o.OrderID == orderID
      select new { Prio = 1, CustomerID = o.CustomerID, CustomerName = o.ShipName, Address = o.ShipAddress, City = o.ShipCity, Region = o.ShipRegion, PostalCode = o.ShipPostalCode, Country = o.ShipCountry }
    ).Union(
      from c in dc.Customers
      where c.Phone = phoneNo
      select new { Prio = 2, CustomerID = c.CustomerID, CustomerName = c.CompanyName, Address = o.Address, City = o.City, Region = o.Region, PostalCode = o.PostalCode, Country = o.Country }
    ).OrderBy(ord => ord.Prio);


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Tuesday, May 25, 2010 4:59 AM
    Answerer
  • PS: When you get the result from your lag test, could you post a comparison LINQ vs SQL.


    Sure Will, but I have to wait on others to do this.. 

    Thanks Again

    dotnetme

    Tuesday, May 25, 2010 4:28 PM