Link to home
Start Free TrialLog in
Avatar of José Perez
José PerezFlag for Chile

asked on

VB.Net/LINQ: ".Contains" condition not finding existing result in MSSQL

Hi,
I have a Linq query that runs a search in 2 tables (through a "Join"):

First table is tblProducts where master data for any product is saved (Name, Barcode, ItemCode, EnglishName, SpanishName, etc.)
Second table is the warehouse/stocks table, every warehouse have specific stock in it so in here we save the warehouse number and the stock of each product.

When I run the search via VB.Net form for a product called "encendedor" (in spanish) it does not appears in the query results:

Dim result = From t1 In objEntities.tblProducts Join t2 In objEntities.tblWarehouseProducts On t1.Barcode Equals t2.Barcode Where ((t2.WarehouseID = Warehouse) And (t2.InStock > 0)) And (t1.Barcode.Contains(CodBarra) And (t1.SpanishName.Contains(Descripcion) And ((t1.ProductCode.Contains(CodigoSAP) And (t1.ExtCatalogNo.Contains(CodProveedor) And (t1.Brand.Contains(Branch))))))) Select t1

Open in new window

take a special attention to:
(t1.SpanishName.Contains(Descripcion) 

Open in new window

where "Descripcion" brings the "encendedor" string.

Now, if I run the query using simple sql commands using SQL Server Management Studio it do finds the result:
select id, USERID, Barcode, ProductCode, ExtCatalogNo, SpanishName, InStock, Brand, Inactive from tblProducts where SpanishName like 'encendedor%'

Open in new window


One thing I have noticed is that if I don't add the '%' at the end of the simple sql command in SQL Server Management Studio it does not finds the result either, so how can I fix my Linq query to find it?

Please your help experts.
Thanks.
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Hi Jose;

I suspect that you are getting a compile time error on your Linq query because the Contains operator takes a collection of x and returns a Boolean if it finds y in the collection in, x.Contains(y), and in your case t1 is not a collection but a single record.

You state the following, "Now, if I run the query using simple sql commands using SQL Server Management Studio it do finds the result:", but that SQL statement is not exactly like the Linq query for example it does not have the Join's in it.

Can you give more details on what you need the query to accomplish and post the SQL create table for all the tables involve in the query.

Thanks
Avatar of José Perez

ASKER

Queries below and image attached.

Query for tblProducts:
USE [POS]
GO
/****** Object:  Table [dbo].[tblProducts]    Script Date: 06-02-2017 11:38:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblProducts](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [varchar](50) NULL,
	[Barcode] [varchar](50) NULL,
	[ProductCode] [varchar](50) NULL,
	[ExtCatalogNo] [varchar](50) NULL,
	[IntCatalogNo] [varchar](50) NULL,
	[CategoryID] [int] NULL,
	[SubCategoryID] [int] NULL,
	[EnglishName] [varchar](200) NULL,
	[SpanishName] [varchar](200) NULL,
	[Brand] [varchar](50) NULL,
	[InStock] [float] NULL,
	[LastPurchPrice] [float] NULL,
	[LastPurchDate] [datetime] NULL,
	[DefaultProviderName] [varchar](max) NULL,
	[DefaultProviderRUT] [varchar](50) NULL,
	[SellPrice] [float] NULL,
	[SellDate] [datetime] NULL,
	[DateCreated] [datetime] NULL,
	[DateModified] [datetime] NULL,
	[ModifyBy] [varchar](50) NULL,
	[Inactive] [bit] NULL,
	[Manual] [varchar](100) NULL,
	[MaxDiscount] [float] NULL,
 CONSTRAINT [PK_tblProducts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Open in new window

Query for tblWarehouseProducts
USE [POS]
GO
/****** Object:  Table [dbo].[tblWarehouseProduct]    Script Date: 06-02-2017 11:38:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblWarehouseProduct](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [varchar](50) NULL,
	[IDProduct] [int] NULL,
	[Barcode] [varchar](100) NULL,
	[ProductCode] [varchar](200) NULL,
	[ExtCatalogNo] [varchar](50) NULL,
	[WarehouseID] [int] NULL,
	[InStock] [int] NULL,
	[DateCreated] [datetime] NULL,
	[DateModified] [datetime] NULL,
 CONSTRAINT [PK_tblWarehouseProduct] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Open in new window

Image attached.
Queries.PNG
Jose, how about what your query needs to accomplish?
Enumerable.Contains performs a 'Like' operation so the %<term>% format is assumed.

Most likely your issue is that Contains is performing a case-sensitive search.  That being said because the Enumerable.Contains would be used on your string for case-insensitive comparison instead of String.Contains, we have to use IndexOf in order to obtain the correct results; e.g. -
Module Module1
    Sub Main()
        Dim items = {
            New With {.ID = 1, .Name = "Item1", .Description = "This is a description of Item1."},
            New With {.ID = 2, .Name = "iTem2", .Description = "This is a description of iTem2."},
            New With {.ID = 3, .Name = "itEm3", .Description = "This is a description of itEm3."},
            New With {.ID = 4, .Name = "iteM4", .Description = "This is a description of iteM1."}
        }

        Dim caseMatters = (From item In items Where item.Description.Contains("Item") Select item)
        Dim caseDoesNotMatter = (From item In items Where item.Description.IndexOf("item", StringComparison.OrdinalIgnoreCase) <> -1 Select item)

        Console.WriteLine("Case Matters -")
        For Each item In caseMatters
            Console.WriteLine(item)
        Next

        Console.WriteLine()
        Console.WriteLine("Case Does Not Matter -")
        For Each item In caseDoesNotMatter
            Console.WriteLine(item)
        Next

        Console.ReadLine()
    End Sub
End Module

Open in new window

Which produces the following output -User generated image
-saige-
Hi Jose;

See if this is what you are looking for.
Dim result = From t1 In objEntities.tblProducts 
             Join t2 In objEntities.tblWarehouseProducts On t1.Barcode Equals t2.Barcode 
             Where ((t2.WarehouseID = Warehouse) And (t2.InStock > 0)) And (t1.Barcode = CodBarra) And (t1.SpanishName = Descripcion) And ((t1.ProductCode = CodigoSAP) And (t1.ExtCatalogNo = CodProveedor) And (t1.Brand = Branch))))))) 
             Select t1

Open in new window

@it_saige
I used the ".toLower" command and it still does not finds it. See query here:
Dim result = From t1 In objEntities.tblProducts Join t2 In objEntities.tblWarehouseProducts On t1.Barcode Equals t2.Barcode Where ((t2.WarehouseID = Warehouse) And (t2.InStock > 0)) And (t1.Barcode.Contains(CodBarra) And (t1.SpanishName.ToLower.Contains(Descripcion.ToLower) And ((t1.ProductCode.Contains(CodigoSAP) And (t1.ExtCatalogNo.Contains(CodProveedor) And (t1.Brand.Contains(Branch))))))) Select t1

Open in new window


@Fernando Soto
The query returns 0 rows (without this change it returns 2 rows)
Do you have sample data that I can load the two tables with and the variables that you use in the Linq query with.
@Fernando Soto
Attached is an exported version in Excel ("Export Data") from SQL Server Management Studio. It contains both tables.
The original LINQ query (in a VB.Net WinForm) is:
'Dim result = From t1 In objEntities.tblProducts Join t2 In objEntities.tblWarehouseProducts On t1.Barcode Equals t2.Barcode Where ((t2.WarehouseID = Warehouse) And (t2.InStock > 0)) And (t1.Barcode.Contains(CodBarra) And (t1.SpanishName.Contains(Descripcion) And ((t1.ProductCode.Contains(CodigoSAP) And (t1.ExtCatalogNo.Contains(CodProveedor) And (t1.Brand.Contains(Branch))))))) Select t1

Open in new window


and the the variables are:
Warehouse = 1
CodBarra = ""
Descripcion = "encendedor"
CodigoSAP = ""
CodProveedor = ""
Branch = ""
201702-06-export_tblProducts_tblWare.xls
Do you have a way to import the Excel worksheets in to SQL server?
Yes, see attached.
In SQL Server Management Studio /Database/(Right Click)/Tasks/Import Data/ and selecting Data Source as "Excel".
Hi Jose;

I have run your original query
Dim result = From t1 In tblProducts 
             Join t2 In tblWarehouseProducts On t1.Barcode Equals t2.Barcode 
			 Where ((t2.WarehouseID = Warehouse) And 
			       (t2.InStock > 0)) And 
				   (t1.Barcode.Contains(CodBarra) And 
				   (t1.SpanishName.Contains(Descripcion) And 
				   ((t1.ProductCode.Contains(CodigoSAP) And 
				   (t1.ExtCatalogNo.Contains(CodProveedor) And 
				   (t1.Brand.Contains(Branch))))))) 
			 Select t1

Open in new window

and looking at it, it seems to be correct with the parameters you have provided returning two rows. Can you please show what you are expecting?
3 rows should be returned, see attached.
If I run an SQL query (in SQL Managemenet Studio) using condition:
Like '%encendedor%'

Open in new window

it returns correctly.
Captura.PNG
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Brilliant! You are a blessing @Fernando Soto!
Thank you for all your effort and patience :)
Really happy with the solution!
Not a problem Jose, glad to help.

Please close this question by selecting the post which answered your question.

Thank you.