Many-To-Many Query with Linq-To-NHibernate
- by rjygraham
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
