none
In statement in Where Condition Linq RRS feed

  • Question


  • Hi,

    i have Sql query like

    @daclare @strProudct varchar(50)
    set @strProduct = ('P1','P2",'P3");

    select * from Product where Pruduct in @strProduct

    or

    select * from Product where Pruduct in  ('P1','P2",'P3")



    please help me how to Convert qeary into  Linq.

    Sanjay G. Patel
    Saturday, July 11, 2009 11:47 AM

Answers

  • You can define a string array and use the Contains method. Here is an example against the Northwind database:

    string[] productNames = { "Aniseed Syrup", "Ikura", "Gumbär Gummibärchen" };
    IQueryable<Products> products =
      from prod in dataContext.Products
      where productNames.Contains(prod.ProductName)
      select prod;
    LINQ to SQL translates that as follows:

    DECLARE @p0 NVarChar(13) SET @p0 = 'Aniseed Syrup'
    DECLARE @p1 NVarChar(5) SET @p1 = 'Ikura'
    DECLARE @p2 NVarChar(19) SET @p2 = N'Gumbär Gummibärchen'
    -- EndRegion
    SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
    FROM [Products] AS [t0]
    WHERE [t0].[ProductName] IN (@p0, @p1, @p2)
    

    MVP XML My blog
    Saturday, July 11, 2009 12:14 PM

All replies

  • You can define a string array and use the Contains method. Here is an example against the Northwind database:

    string[] productNames = { "Aniseed Syrup", "Ikura", "Gumbär Gummibärchen" };
    IQueryable<Products> products =
      from prod in dataContext.Products
      where productNames.Contains(prod.ProductName)
      select prod;
    LINQ to SQL translates that as follows:

    DECLARE @p0 NVarChar(13) SET @p0 = 'Aniseed Syrup'
    DECLARE @p1 NVarChar(5) SET @p1 = 'Ikura'
    DECLARE @p2 NVarChar(19) SET @p2 = N'Gumbär Gummibärchen'
    -- EndRegion
    SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
    FROM [Products] AS [t0]
    WHERE [t0].[ProductName] IN (@p0, @p1, @p2)
    

    MVP XML My blog
    Saturday, July 11, 2009 12:14 PM


  • hello Martin

    Thanx for Reply


    i try your solution but i get error like
    'I get the following NoSupportedException: Only arguments that can be evaluated on the client are supported for the String.Contains Method."


    can u help me more ?

    Sanjay G. Patel
    Saturday, July 11, 2009 1:00 PM
  • Are you using LINQ to SQL? Can you show us the exact statement that causes that error?
    MVP XML My blog
    Saturday, July 11, 2009 3:23 PM