Linq GroupBy

  • Thread starter Thread starter Jakob Lithner
  • Start date Start date
J

Jakob Lithner

I have three tables in my database: Person, Role and PersonRole where
PersonRole is a many-to-many relation to set roles for each person.
The tables have integer primary keys named PersonID and RoleID.

Now I want to load a checkboxlist for a specific person with all roles
visible and relevant roles checked.

To do that I tried the following code in Linq:

var personRoles = dc.PersonRoles.Where(pr => pr.PersonID == personID);

var allRoles = from r in dc.Roles
join pr in personRoles on r.RoleID equals pr.RoleID into roleGroup
from g in roleGroup.DefaultIfEmpty()
select new { g.RoleID, g.Role.RoleName, Selected = g.PersonID != 0 };

return allRoles.ToList();


The code compiles fine but in runtime produces an InvalidOperationException:
The null value cannot be assigned to a member with type System.Int32 which is
a non-nullable value type.

1) Why is that?
I feel I am pretty close but something must be wrong.

2) Could I condense the code even more by filtering on personID in the last
query? I tried that but never succeeded.
 
Hello Jakob,

Thanks for using Microsoft Newsgroup Support Service, my name is Colbert
Zhou [MSFT] and I will be working on this issue with you.

I can reproduce your issue and the following are my explanations, as well
as a simple solution to achieve your objective.
If we print the allRoles SQL command, we can get the following,

{SELECT [t1].[RoleID] AS [RoleID], [t2].[RoleName] AS [RoleName],
(CASE
WHEN [t1].[PersonID] <> @p1 THEN 1
WHEN NOT ([t1].[PersonID] <> @p1) THEN 0
ELSE NULL
END) AS [Selected]
FROM [dbo].[Role] AS [t0]
LEFT OUTER JOIN [dbo].[PersonRole] AS [t1] ON ([t0].[RoleId] =
[t1].[RoleID]) AND ([t1].[PersonID] = @p0)
LEFT OUTER JOIN [dbo].[Role] AS [t2] ON [t2].[RoleId] = [t1].[RoleID]
}
We can see that it uses the left outer join which causes that the returned
collection sometimes includes a null row. I have a test in my side and see
the following returned value in SQL,
1 Admin 1,
2 Operator 1,
NULL NULL NULL

So we are trying to assign the third row (null value) to a System.Int32
type list's item. As a result, it throws the exception.

I have found a very simple and clean solution to achieve this objective by
using Extension Method. See my codes as follows,
namespace CustomExtensions
{
public static class IntExtension
{
public static bool InCollection(this int value, Array a)
{
for(int index = 0; index < a.Length; index++)
{
if(Convert.ToInt32(a.GetValue(index))==value)
{
return true;
}
}
return false;
}
}
}


namespace WindowsFormsApplication1
{
using CustomExtensions;

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
using (PersonRolesDataContext dc = new PersonRolesDataContext())
{
var personRoles = dc.PersonRoles.Where(pr => pr.PersonID ==
1).Select (pr=>pr.RoleID );

var allRoles = from r in dc.Roles
select new { r.RoleId, r.RoleName, Selected
= r.RoleId.InCollection(personRoles.ToArray()) };

allRoles.ToList();
}
}
}
}

I write an extension method for int type. This method can tell if a value
is in a Array collection. So our LINQ codes will be very simple. We just
need to select from the Roles table and add a new column via "Selected =
r.RoleId.InCollection(personRoles.ToArray())".

Please let me know if my explanations address your concern? If you have any
future questions or concerns on this, please feel free to let me know!


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks for quick reply!

In fact I did a very similar solution myself that stored the first result in
an array and then for each role record checks if it exists in the person
array. But I still think this is a workaround.

Is there really no way to create a straight query that creates the desired
result?

After all there is a simple SQL query that produces what I want:

SELECT Role.RoleID, personRoles.PersonID
FROM Role LEFT OUTER JOIN personRoles ON Role.RoleID = personRoles.RoleID

Where personRoles is a view filtered on desired PersonID.

Is it really not possible to perform a similar query in Linq-to-SQL?
 
Hello Jakob,

Just need a little modification of your original LINQ codes. The following
has worked in my side.
using (PersonRolesDataContext dc = new PersonRolesDataContext())
{
var personRoles = dc.PersonRoles.Where(pr => pr.PersonID ==
1);

var allRoles = from r in dc.Roles
join pr in personRoles on r.RoleId equals
pr.RoleID into roleGroup
from g in roleGroup.DefaultIfEmpty()
select new {
r.RoleId,
r.RoleName,
Selected =(g==null?false:true)
};

allRoles.ToList();
}

Note in the select clause we need to use r.RoleId and r.RoleName instead of
g.RoleID, g.Role.RoleName. The latter one will cause the returned rows
include NULL. As a result, we meet the error mentioned in the first post.

And for the Selected column in the select clause, we need to test if it is
null. If yes, set the Selected to false, otherwise set it to true.

Please have a try and let me know if it works for you? Have a nice day!

Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
Great !!!

The exchange of "g" with"r" in the select clause made the whole difference!

Thanks!
 
Back
Top