String treated as NVarchar instead of Varchar

Posts   
 
    
Posts: 25
Joined: 08-Mar-2016
# Posted on: 02-Nov-2016 19:39:59   

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();

//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'

My database columns are varchar columns, so this is giving me performance issues.

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

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 03-Nov-2016 00:07:53   

In the Designer, do these fields appear to have a DB Type of varchar or nvarchar?

Posts: 25
Joined: 08-Mar-2016
# Posted on: 03-Nov-2016 15:29:51   

DBType: varchar

Resulting .NET type : System.String

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 03-Nov-2016 16:42:01   

Reproduced.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 04-Nov-2016 14:18:58   

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

Frans Bouma | Lead developer LLBLGen Pro
Posts: 25
Joined: 08-Mar-2016
# Posted on: 07-Nov-2016 20:20:54   

Thanks a ton for the detailed explanation

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();
}

this worked like a charm!