The generated SQL does a cross join but as the ID's are matched it acts like an inner join but is there a better performing way to do this by actually using the join keyword in C#? Is there a way where you don't have to specify how the properties join because they are all heirarchicly related anyway
Item is a Page class
PageRoles is an IQueryable property in a Page class
aspnet_Roles is an IQueryable property in a PageRole class
var item = _repository.GetByKey(999);
var f = (from fd in item.PageRoles
from k in fd.aspnet_Roles
where Roles.GetRolesForUser().Contains(k.RoleName)
&& k.RoleId == fd.RoleId
select k.RoleName)
.Count();
EDIT:
Here is an example of an IQueryable property in my classes. The below example comes from the PageRole class
public IQueryable<aspnet_Role> aspnet_Roles
{
get
{
var repo=NorthCadburyWebsite.Models.aspnet_Role.GetRepo();
return from items in repo.GetAll()
where items.RoleId == _RoleId
select items;
}
}