Parameters problem

Parameters problem

Postby dilbert » Thu 21 Jun 2012 14:38

I ran into another problem with LinqConnect 4.x (4.0.21)

Please, consider following queries:
Code: Select all
int? idUser = null;
DB.Configurations.First(c => c.Config_key.Equals("ABC") && c.Id_user == idUser);
DB.Configurations.First(c => c.Config_key.Equals("ABC") && c.Id_user == idUser);
idUser = 1;
DB.Configurations.First(c => c.Config_key.Equals("ABC") && c.Id_user == idUser);
idUser = null;
DB.Configurations.First(c => c.Config_key.Equals("ABC") && c.Id_user == idUser);



Here is their SQL translation:

Code: Select all
SELECT ... FROM "main".configuration t1
WHERE (t1.Config_key = :p0) AND (t1.Id_user IS NULL) LIMIT 1
-- p0: Input Text (Size = 18; DbType = String) [ABC]

SELECT ... FROM "main".configuration t1
WHERE (t1.Config_key = :p0) AND (t1.Id_user IS NULL) LIMIT 1
-- p0: Input Text (Size = 18; DbType = String) [ABC]

SELECT ... FROM "main".configuration t1
WHERE (t1.Config_key = :p0) AND (t1.Id_user = :p1) LIMIT 1
-- p0: Input Text (Size = 18; DbType = String) [ABC]
-- p1: Input Int32 (Size = 0; DbType = Int32) [6]

SELECT ... FROM "main".configuration t1
WHERE (t1.Config_key = :p0) AND (t1.Id_user = :p1) LIMIT 1
-- p0: Input Text (Size = 18; DbType = String) [ABC]
-- p1: Input Int32 (Size = 0; DbType = Int32) []



All queries are the same. But the third one has non-null value in second parameter. After executing this query the second parameter persists in next queries regardless of a value of this parameter. Therefore the result of the fourth query is wrong (different from first and second queries).

This behavior is same with SQLite and MySql provider. There was no such a bug in previous versions of LinqConnect (3.x).
dilbert
 
Posts: 68
Joined: Tue 28 Apr 2009 10:11

Re: Parameters problem

Postby MariiaI » Fri 22 Jun 2012 09:27

We have reproduced this issue. We will investigate it and and inform you when it is fixed.
This issue related to the query cache. As a workaround, you could disable it, for example remove (or comment) the next line in the DataContext.Designer.cs file:
Code: Select all
public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof([YourDataContextType]));

For more information about query cache please refer to http://www.devart.com/linqconnect/docs/?CompiledQueries.html
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Parameters problem

Postby MariiaI » Mon 02 Jul 2012 09:59

The bug, related to the problems with query cache, has been fixed. It will be available in the next build of LinqConnect, which we plan to release next week.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Parameters problem

Postby MariiaI » Thu 19 Jul 2012 06:55

New build of LinqConnect 4.0.45 is available for download now!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=24531
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Parameters problem

Postby Goransi » Tue 04 Mar 2014 09:31

We're experiencing the same issue with Entity Developer 5.7.261, LinqConnect (standard) 4.4.403.0.

Can you confirm this issue regressed or if there's anything wrong on our part. It's quite easy to reproduce:
1)
Code: Select all
int? bankAccountId = null
persons.Where(person => person.BANK_ACCOUNT_ID == bankAccountId).ToList();
bankAccountId = 123
persons.Where(person => person.BANK_ACCOUNT_ID == bankAccountId).ToList();


produces query which states "IS NULL" on both queries.

2)
Code: Select all
int? bankAccountId = 123
persons.Where(person => person.BANK_ACCOUNT_ID == bankAccountId).ToList();
bankAccountId = null
persons.Where(person => person.BANK_ACCOUNT_ID == bankAccountId).ToList();


produces query which states "= 123" on first query and "= NULL" on second.
Goransi
 
Posts: 10
Joined: Thu 23 Jan 2014 22:48

Re: Parameters problem

Postby MariiaI » Tue 04 Mar 2014 13:31

Thank you for the report. We have reproduсed this issue. We will inform you when it is fixed.

As a workaround, please disable the compiled query cache. To use it please open your model by double-clicking the *.lqml file, then expand the Templates node in Model Explorer, right-click the "LinqConnect" template, and select properties from the shortcut menu, set the "Use Compiled Query Cache" property to False and save the changes.
In this case this line in the *.Designer.cs file will not be generated:
Code: Select all
public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof(YourDataContext));
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Parameters problem

Postby MariiaI » Fri 07 Mar 2014 06:31

The bug with the query cache when performing several queries, having nullable parameters, sequentially is fixed.
New build of LinqConnect 4.4.453 is available!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=29103.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)