Hey guys,
I have been stumped for a few days on how to write a linq query that
will return a list of projects where any of its association contains
one of the items passed in from the User association. In other words,
I need to return a list of Projects that contain ProjectCodes that
belong to that User. Both the Project and User classes have a many to
many relationship on ProjectCode.
Shortened Model:
public class Project
{
public virtual IList<ProjectCode> ProjectCodes { get; set; }
}
public class ProjectCode
{
public virtual int Id { get; set; }
public virtual string Name { get; set; }
}
public class User
{
public virtual IList<ProjectCode> ProjectCodes {get; set; }
}
Using Fluent to Map:
public ProjectMap()
{
HasManyToMany<ProjectCode>(c => c.ProjectCodes)
.LazyLoad()
.WithTableName("ProjectCodesByProject")
.WithChildKeyColumn("ProjectCode")
.WithParentKeyColumn("ProjectID")
.FetchType
.Join();
}
public UserMap()
{
HasManyToMany<ProjectCode>(c => c.ProjectCodes)
.LazyLoad()
.WithTableName("ProjectCodesByDesktop")
.WithChildKeyColumn("ProjectCode")
.WithParentKeyColumn("UserID")
.FetchType
.Join();
}
I have an extension method that passes in the List of ProjectCodes
associated with the User:
public static IQueryable<Project> WithProjectCodes(this
IQueryable<Project> qry, IList<ProjectCode> pCodes)
{
var projects =
from p in qry
where p.ProjectCodes.Contains(pCodes[0])
select p;
}
This query only returns the first ProjectCode that matches. I need it
to return all projects where ANY ProjectCode matches- basically an IN
statement needs to be generated. I believe the NHibernate LINQ
provider is working properly, I just can't seem to dynamically create
the proper linq query. For example this works, but only if I know 3
ProjectCodes are being sent in:
public static IQueryable<Project> WithProjectCodes(this
IQueryable<Project> qry, IList<ProjectCode> pCodes)
{
var projects =
from p in qry
where p.ProjectCodes.Contains(pCodes[0]) || p.CSICodes.Contains
(pTrades[1]) || p.CSICodes.Contains(pTrades[2])
select p;
}
That query returns the right results, but how do I dynamically create
the correct query? I've tried PredicateBuilder, but it fails on the
Linq provider: unsupported operation on Invoke(). Any and all ideas
are welcome.
Thanks for the help,
-Dallas
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
