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

Question

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'm not even sure it would work that way.

Thanks in advance to anyone who can help.

Ryan

Answer 1

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.