santuvssantu wrote:
Hello,
I'm writing LINQ queries with llblgen and encountering an issue with string as nvarchar.
My C# object myobject of type LocationObject
My sample query:
using (var db = new DataAccessAdapter())
{
var linq = new LinqMetaData(db);
var query = (from l in linq.Location
join r in linq.RefLocation on l.LocationId equals r.Id
select new { r.Code, r.Description, l.Details }
).AsQueryable();
No need for AsQueryable(), the query itself is already a queryable.
//Dynamic where based on conditions
if(myobject.work == "N")
{
query = query.Where(e => e.Code == myobject.code);
if(myobject.type == "S")
{
var desc = myobject.desc;
query = query..Where(e => e.Details.Contains(desc));
}
}
var results = query.ToList();
}
When the final query gets executed, the variables are declared like below: (Formatted for reference)
@p1 nvarchar(10),@p3 nvarchar(3)
@p1=N'LOC123' , SET @p3=N'RES'
From where did you copy this? As traces produced by the runtime don't use these specifications. Did you copy this from the SQL profiler?
My database columns are varchar columns, so this is giving me performance issues.
You're using 'Contains' which will result in a LIKE predicate, using %pattern%. So this will likely miss any indexes, if you have specified any. You say you run into performance issues, but due to what are these caused? LIKE predicates tend to be slow. If you have to search for a lot of elements, think about using full text search instead.
Is there any way I can force this to consider as varchar?
I'm using Llblgen Pro 5.0 Final version (Adapter)
Backend SQL Server 2012 and Visual Studio 2013 with 4.5 Framework.
ORMClasses Dll -> 5.0.6.0
It actually already tries to do its best to determine the type of the field used in the LIKE parameter. I see what's wrong though. First what it can do:
query on a field which is mapped to nvarchar:
// query
var metaData = new LinqMetaData(adapter);
var q = from c in metaData.Customer
where c.Country.Contains("e")
select c;
// results from trace:
Generated Sql query:
Query: SELECT [LPA_L1].[Address], [LPA_L1].[City], [LPA_L1].[CompanyName], [LPA_L1].[ContactName], [LPA_L1].[ContactTitle], [LPA_L1].[Country], [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L1].[Fax], [LPA_L1].[Phone], [LPA_L1].[PostalCode], [LPA_L1].[Region] FROM [Northwind].[dbo].[Customers] [LPA_L1] WHERE ( ( [LPA_L1].[Country] LIKE @p1))
Parameter: @p1 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "%e%".
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
As you can see, unicode string parameter.
query on a field mapped to varchar:
// query
var metaData = new LinqMetaData(adapter);
var q = from c in metaData.Clerk
where c.JobDescription.Contains("e")
select c;
// results from trace
Generated Sql query:
Query: SELECT DISTINCT [LPA_L1].[EmployeeID] AS [F7_0], [LPA_L1].[Name] AS [F7_1], [LPA_L1].[StartDate] AS [F7_2], [LPA_L1].[WorksForDepartmentID] AS [F7_3], [LPA_L2].[ClerkID] AS [F4_4], [LPA_L2].[JobDescription] AS [F4_5] FROM ( [InheritanceTwo].[dbo].[Employee] [LPA_L1] INNER JOIN [InheritanceTwo].[dbo].[Clerk] [LPA_L2] ON [LPA_L1].[EmployeeID]=[LPA_L2].[ClerkID]) WHERE ( ( [LPA_L2].[JobDescription] LIKE @p1) AND ( [LPA_L2].[ClerkID] IS NOT NULL))
Parameter: @p1 : AnsiString. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "%e%".
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
As you can see, ansi string parameter, so it looks at the field returned.
Your query however is a little different: here, you first project the results in an anonymous type. Then, you append where clauses to the query and the predicates use Contains, so will result in a LIKE predicate. However, the field filtered isn't the entity field, but the projected field in the anonymous type, which is of type 'string' and there's no other info. So it falls back onto the default type which is nvarchar.
To get it properly use the type of the field, don't append the where clauses on the projected query but before that:
using (var db = new DataAccessAdapter())
{
var linq = new LinqMetaData(db);
var refLocation = linq.RefLocation;
var location = linq.Location;
if(myobject.work == "N")
{
refLocation = refLocation.Where(e => e.Code == myobject.code);
if(myobject.type == "S")
{
var desc = myobject.desc;
location = location.Where(e => e.Details.Contains(desc));
}
}
var query = (from l in location
join r in refLocation on l.LocationId equals r.Id
select new { r.Code, r.Description, l.Details }
);
var results = query.ToList();
}
or thereabout. Here I append the filters on the entities, not the projected result. The LIKE predicate will now use the field of the entity to determine the type of the parameter.
Though I doubt the performance issues are caused by the nvarchar->varchar cast done one time by the sql server engine, but simply by the LIKE parameter itself which is slow on many rows as it can bypass indexes