views:

440

answers:

3

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;
    }
}
A: 

I don't have my Intellisense with me but I think you can:

var f = (from fd in item.PageRoles 
         join k in fd.aspnet_Roles on k.RoleId equals fd.RoleId
         where Roles.GetRolesForUser().Contains(k.RoleName) 
         select k.RoleName)
        .Count();
Robert Harvey
Change the == to equals, and you'll have to swap so fd.RoleId is on the left and k.RoleId is on the right, but yes I believe this is correct. I'm still trying to get my head around the purpose of the join and the hierarchy tho...
lc
Thanks..........
Robert Harvey
you can't do join k in fd.aspnet_Roles because for some reason it doesn't recognise fd...?
Jon
I think I was right the first time... The K.RoleID goes first before the equals.
Robert Harvey
Maybe but fd.aspnet_roles throws an error
Jon
A: 

Does this compile or do you get an error?

In this case, as you mentioned, you don't need a join. I haven't used this syntax, but it looks like you use k as a refererence (like an alias in sql syntax)

This should deliver the same result (without even using k):

var f = (from fd in item.PageRoles 
         where Roles.GetRolesForUser().Contains(fd.aspnet_Roles.RoleName) 
         && fd.aspnet_Roles.RoleId == fd.RoleId 
         select fd.aspnet_Roles.RoleName)
        .Count();

Note: Not tested, just a guess.

SchlaWiener
Can't do fd.aspnet_Roles.RoleName as fd.aspnet_Roles is IQueryable and not an instance of the class
Jon
A: 

I think I'm confused, because I'm seeing the following hierarchy in your query which isn't making any sense:

[PageRoles]
|--RoleId <~~~~~~~~~.
|--[aspnet_Roles]   |
   |--RoleId ~~~~~~~*
   |--RoleName


Is the hierarchy determined by the RoleId and you only mean for PageRoles.RoleId to reference aspnet_Roles.RoleId? So you might have something like:

[PageRoles]  [aspnet_Roles]
|--RoleId    |--RoleId
             |--RoleName
And you're trying to make it into the following:
[PageRoles]
|--RoleId 
|--[aspnet_Roles] (where RoleId == PageRoles.RoleId)
   |--RoleName

...?


In which case if you can't do the following, then something is wrong with your IQueryable property definitions and not the query you have posted. Namely the method to get PageRoles's aspnet_Roles child rows is not set up properly.

var f = (from fd in item.PageRoles 
         from k in fd.aspnet_Roles 
         where Roles.GetRolesForUser().Contains(k.RoleName) 
         select k.RoleName)
        .Count();

(fd.aspnet_Roles should already be constrained by RoleId and generate an inner join).

You can check your hierarchy by seeing what SQL is generated from:

var f = from fd in item.PageRoles
        from k in fd.aspnet_Roles
        select k;

which should vaguely resemble (with something having to do with item thrown in):

SELECT k.RoleName, k.SomethingElse
FROM PageRoles fd
INNER JOIN aspnetRoles k ON fd.RoleId = k.RoleId;


I'm sure I'm missing something, so please help me fill in the blanks...

lc
var f = from fd in item.PageRoles from k in fd.aspnet_Roles select k;producesa cross join not an inner join thats why i otrignally questioned the need for the roleid == roledid.Is this a bug with Subsonic then?
Jon
Ok, now I got you. Not sure if it's a bug yet; can you post your IQueryable get methods?
lc
see my edited question
Jon