Many-To-Many Query with Linq-To-NHibernate

Ok guys (and gals), this one has been driving me nuts all night and I'm turning to your collective wisdom for help.

I'm using Fluent Nhibernate and Linq-To-NHibernate as my data access story and I have the following simplified DB structure:

CREATE TABLE [dbo].[Classes](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](100) NOT NULL,
 [StartDate] [datetime2](7) NOT NULL,
 [EndDate] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)

CREATE TABLE [dbo].[Sections](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [ClassId] [bigint] NOT NULL,
 [InternalCode] [varchar](10) NOT NULL,
 CONSTRAINT [PK_Sections] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)

CREATE TABLE [dbo].[SectionStudents](
 [SectionId] [bigint] NOT NULL,
 [UserId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_SectionStudents] PRIMARY KEY CLUSTERED 
(
 [SectionId] ASC,
 [UserId] ASC
)

CREATE TABLE [dbo].[aspnet_Users](
 [ApplicationId] [uniqueidentifier] NOT NULL,
 [UserId] [uniqueidentifier] NOT NULL,
 [UserName] [nvarchar](256) NOT NULL,
 [LoweredUserName] [nvarchar](256) NOT NULL,
 [MobileAlias] [nvarchar](16) NULL,
 [IsAnonymous] [bit] NOT NULL,
 [LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
 [UserId] ASC
)

I omitted the foreign keys for brevity, but essentially this boils down to:

• A Class can have many Sections.
• A Section can belong to only 1 Class but can have many Students.
• A Student (aspnet_Users) can belong to many Sections.

I've setup the corresponding Model classes and Fluent NHibernate Mapping classes, all that is working fine.

Here's where I'm getting stuck. I need to write a query which will return the sections a student is enrolled in based on the student's UserId and the dates of the class.

Here's what I've tried so far:

1.

var sections = (from s in this.Session.Linq<Sections>()
where s.Class.StartDate <= DateTime.UtcNow
&& s.Class.EndDate > DateTime.UtcNow
&& s.Students.First(f => f.UserId == userId) != null
select s);

2.

var sections = (from s in this.Session.Linq<Sections>()
where s.Class.StartDate <= DateTime.UtcNow
&& s.Class.EndDate > DateTime.UtcNow
&& s.Students.Where(w => w.UserId == userId).FirstOrDefault().Id == userId
select s);

Obviously, 2 above will fail miserably if there are no students matching userId for classes the current date between it's start and end dates...but I just wanted to try.

The filters for the Class StartDate and EndDate work fine, but the many-to-many relation with Students is proving to be difficult. Everytime I try running the query I get an ArgumentNullException with the message:

Value cannot be null. Parameter name: session

I've considered going down the path of making the SectionStudents relation a Model class with a reference to Section and a reference to Student instead of a many-to-many. I'd like to avoid that if I can, and I'开发者_StackOverflow社区m not even sure it would work that way.

Thanks in advance to anyone who can help.

Ryan

For anyone who cares, it looks like the following might work in the future if Linq-To-NHibernate can support subqueries (or I could be totally off-base and this could be a limitation of the Criteria API which is used by Linq-To-NHibernate):

var sections = (from s in session.Linq<Section>()
where s.Class.StartDate <= DateTime.UtcNow
&& s.Class.EndDate > DateTime.UtcNow
&& s.Students.First(f => f.UserId == userId) != null
select s);

However I currently receive the following exception in LINQPad when running this query:

Cannot use subqueries on a criteria without a projection.

So for the time being I've separated this into 2 operations. First get the Student and corresponding Sections and then filter that by Class date. Unfortunately, this results in 2 queries to the database, but it should be fine for my purposes.