José Perez
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:
Now, if I run the query using simple sql commands using SQL Server Management Studio it do finds the result:
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.
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
take a special attention to:(t1.SpanishName.Contains(Descripcion)
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%'
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.
ASKER
Queries below and image attached.
Query for tblProducts:
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
Query for tblWarehouseProductsUSE [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
ASKER
Image attached.
Queries.PNG
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. -
-saige-
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
Which produces the following output --saige-
Hi Jose;
See if this is what you are looking for.
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
ASKER
@it_saige
I used the ".toLower" command and it still does not finds it. See query here:
@Fernando Soto
The query returns 0 rows (without this change it returns 2 rows)
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
@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.
ASKER
@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:
and the the variables are:
Warehouse = 1
CodBarra = ""
Descripcion = "encendedor"
CodigoSAP = ""
CodProveedor = ""
Branch = ""
201702-06-export_tblProducts_tblWare.xls
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
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?
ASKER
Yes, see attached.
In SQL Server Management Studio /Database/(Right Click)/Tasks/Import Data/ and selecting Data Source as "Excel".
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
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
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?
ASKER
3 rows should be returned, see attached.
If I run an SQL query (in SQL Managemenet Studio) using condition:
Captura.PNG
If I run an SQL query (in SQL Managemenet Studio) using condition:
Like '%encendedor%'
it returns correctly.Captura.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant! You are a blessing @Fernando Soto!
Thank you for all your effort and patience :)
Really happy with the solution!
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.
Please close this question by selecting the post which answered your question.
Thank you.
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