I've been using NHibernate and Castle ActiveRecord for a few months,
building a complex web application with it. In my repository, I've
been using DetachedCriteria and ICriteria with many hindrances, so I
decided to experiment with LINQ because of popularity (meaning more
helpful information when searching on Google, which proven most
valuable). I find LINQ to be very pleasing with strongly-typed syntax
(newbie friendly).
Even though, I found a solution to my query through trial and error. I
found some limitations and bugs with NHibernate.Linq. Here's my setup,
I have a many to many relationship with an association table between:
Group.cs, GroupMember.cs, User.cs
Using plain SQL Syntax (what I'm trying to archive):
SELECT
groupName
FROM
Groups
WHERE
groupId NOT IN (
SELECT memberGroupId
FROM GroupsMembers
WHERE memberUserId = '3e0d22d2-83c9-443d-978b-9c7000264f74'
)
Using NHibernate DetachedCriteria and ICriteria (the query works, but
took me many hours to figure out because lack of information/
documentation):
public static IList<Models.Group> GetGroupsNotByMember(Guid userId) {
return Models.Group.FindAll(DetachedCriteria.For<Models.Group>().Add(
Restrictions.Eq("groupIsDeleted", false)
).Add(
Subqueries.PropertyNotIn("groupId",
DetachedCriteria.For<Models.GroupMember>().SetProjection(
Projections.Property("memberGroup.groupId")
).Add(
Restrictions.Eq("memberUser.userId", userId)
))
));
}
Most ideal solution using LINQ, but got an error
"System.ArgumentException : Unhandled Expression Type: 1000", probably
NHibernate.Linq is limited:
public static IList<Models.Group> GetGroupsNotByMember(Guid userId) {
return (
from g in Models.Group.Queryable
where g.groupIsDeleted == false && !(
from gm in Models.GroupMember.Queryable
where gm.memberUser.userId == userId
select gm.memberGroup.groupId
).Contains(g.groupId)
select g
).ToList();
}
Workaround with LINQ (causing to send two queries to the database):
public static IList<Models.Group> GetGroupsNotByMember(Guid userId) {
var memberGroups = (
from g in Models.GroupMember.Queryable
where g.memberUser.userId == userId
select g.memberGroup.groupId
).ToArray();
return (
from g in Models.Group.Queryable
where g.groupIsDeleted == false && memberGroups.Contains
(g.groupId)
select g
).ToList();
}
>From debugging NHibernate.Linq, I found a query containing unnecessary
things, the "not" clause on groupIsDeleted and multiple parenthesises.
I think it's a bug:
... WHERE (not ((this_.groupIsDeleted = ?p0)) and not ((this_.groupId
in (?p1))));?p0 = True, ?p1 = 29dbf697-cdcb-49a4-ab8a
Specs:
ASP.NET 3.5 SP1 MVC 1.0
MySQL 5.0.24a
MySQL Connector/Net 6.1.1
NHibernate 2.1.0.GA
NHibernate.Linq 1.0.0.GA
Castle ActiveRecord 2.0
Sincerely,
William Chang
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
