Failed to use IGrouping<TKey, TElement>.Contains() from Lookup to make SQL IN statement
Description
Environment
Attachments
Activity
Alex Zaytsev June 8, 2012 at 2:09 PM
Fix commited to master e276db9ca6bee32b432476a2b70065f97640381c
Mike June 1, 2012 at 1:27 PM
MAKE THIS WORK
Mazlan June 9, 2011 at 8:20 PM
Emm...it not only affect v3.1 but until the nightly build release.
I create the test project which I attached above and tested from the current trunk.
It was failed at least in my last checkout reversion 5914, so the new v3.2 beta also the same.
Good luck.
Mazlan June 9, 2011 at 8:12 PM
This is my first time use NHibernate in v3.1 and it was extreamly great than buggy Entity Framework. Logically this issue should persist on previous version, I don't have any idea about the other versions on which Linq is started, change it apprioriately.
This issue also my first issue reported, as NHibernate user and using Linq in NHibernate, when trying to get how to use SQL IN, using the .Contains() extension method is the way found in search engine. So I will thinking any .Contains() (mostly from type IEnumerable<TElement>) can automatically turn into SQL IN statement but not from System.Linq.Lookup. There is a reasons why I forced to use Lookup type. It is hard to deal with database with columns only Id, Key and Value which Value has a type sql_variant. I need to filter out by date, I need to do grouping all in application level. I want to avoid unnecessary conversion to List<T>.
Why I report this, because I love Linq for NHibernate than Linq for SQL. Believe me it is possible to fix this, even I never look into the NHibernate source code. Otherwise you could make an exception that NHibernate does not support using IGrouping<TKey, TElement>.Contains() in Linq statement and suggest to convert to list by .ToList() first, it save a lots of time.
This is my QnA in StackOverflow http://stackoverflow.com/questions/6146706/sql-in-statement-in-linq-for-fluent-nhibernate
http://stackoverflow.com/questions/6146706/sql-in-statement-in-linq-for-fluent-nhibernate
Fabio Maulo June 9, 2011 at 5:09 AM
Just as curiosity.
I saw you have reported the issue even for version 1.2.0 that was released some years ago when LINQ was, perhaps, not even just an idea for some Microsoft guy.
Are you doing it for some special reason or is just to do something cool ?
Details
Details
Assignee
Reporter
Labels
Components
Fix versions
Affects versions
Priority
Minor
This bug still happened in current Revision 5914.
Using array, List or collection does not have a problem to make SQL IN statement by using Contains() method.
However NHibernate does not generete SQL IN statement from IGrouping<TKey, TElement> type which is mostly used from ILookup<TKey, TElement>.
It simply pass it to SQL parameter which then will cause GenericADOException.
Reproduce:
var Emps = (from e in s.Query<Employee>()
select e).ToList();
var EmpsInDept = Emps.ToLookup(e => e.Department, e => e.Id); // e.Department is a type of string.
foreach (var DeptGroup in EmpsInDept)
{
if (DeptGroup.Key == "IT")
{
var firstNameInIT = (from e in s.Query<Employee>()
where DeptGroup.Contains(e.Id)
select e.FirstName).ToList();
}
}
The "DeptGroup.Contains(e.Id)" will throw the exception. Currently convert it to a List temporarily fix it "DeptGroup.ToList().Contains(e.Id)".
Note:
IGrouping<TKey, TElement> is also IEnumerable<TElement> which can be used like this:
List<string> list = new List<string>(DeptGroup);
foreach (string dept in DeptGroup){}
because DeptGroup is also implement IEnumerable<TElement> which is IEnumerable<string>
Suggestion: to build the SQL IN statement do not only look for collection, List or Array type but also any of IEnumerable<TElement> with method Contains().