[nhibernate-issues] [NHJIRA] Commented: (NH-2762) Failed to use IGrouping<TKey, TElement>.Contains() from Lookup to make SQL IN statement

[Mazlan (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2762?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21278#action_21278 ] 

Mazlan commented on NH-2762:
----------------------------

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

> Failed to use IGrouping<TKey, TElement>.Contains() from Lookup to make SQL IN statement
> ---------------------------------------------------------------------------------------
>
>                 Key: NH-2762
>                 URL: http://216.121.112.228/browse/NH-2762
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: Linq Provider
>    Affects Versions: 3.1.0
>            Reporter: Mazlan
>            Priority: Minor
>         Attachments: NH2762.zip
>
>
> 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().

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira