Linq Count() will always return 1 within Visual Studio

Question

Background

Right now I have a system named FoorBar where there are Users, Rooms, and Authorizations.

Detailed Background

A user can have many authorizations and many rooms can be added to a single authorization. To handle this I have the following tables: FooBar_User, FooBar_UserAuth, FooBar_Authorization, FoorBar_AuthType, FooBar_RoomAuth, and Room_Inventory.

I need a query to return the authorization id, the number of rooms a user has, the authorization type, and the expiration date of the authorization.

SQL

The SQL to generate the desired output is below:

SELECT a.AuthId
    ,COUNT(ra.RoomAuthId) AS RmCnt
    ,fat.AuthTypeAbbr
    ,a.ExpirationDate

FROM FooBar_User AS u
    INNER JOIN FooBar_UserAuth AS ua ON u.UserId = ua.UserId
    INNER JOIN FooBar_Authorization AS a ON ua.AuthId = a.AuthId
    INNER JOIN FooBar_AuthType AS fat ON a.AuthTypeId = fat.AuthTypeId
    LEFT JOIN FooBar_RoomAuth AS ra ON a.AuthId = ra.AuthId AND ra.IsActive = 1
    LEFT JOIN Room_Inventory AS ri ON ra.RmId = ri.RMID AND ri.IsActive = 1

WHERE   a.IsActive =  1
    AND u.IsActive =  1
    AND u.UserId   = 10

GROUP BY a.AuthId, fat.AuthTypeAbbr, a.ExpirationDate

LINQ Query

from ua in db.FooBar_UserAuth
join ra in db.FooBar_RoomAuth
    on new { ua.FooBar_Authorization.AuthId, IsActive = true }
    equals new { ra.AuthId, ra.IsActive } into raJoin
from ra in raJoin.DefaultIfEmpty()
join ri in db.Room_Inventory
    on new { ra.RmId, IsActive = true }
    equals new { RmId = ri.RMID, ri.IsActive } into riJoin
from ri in riJoin.DefaultIfEmpty()
where
    ua.FooBar_Authorization.IsActive &&
    ua.IsPi == true &&
    ua.FooBar_User.IsActive &&
    ua.FooBar_User.UserId == 10
group new { ua.FooBar_Authorization, ua.FooBar_Authorization.FooBar_AuthType, ra } by new
{
    AuthId = (int?)ua.FooBar_Authorization.AuthId,
    ua.FooBar_Authorization.FooBar_AuthType.AuthTypeAbbr,
    ua.FooBar_Authorization.ExpirationDate
} into g
select new
{
    AuthId = g.Key.AuthId.Value,
    RmCnt = g.Count(p => p.ra.RoomAuthId != 0),
    AuthType = g.Key.AuthTypeAbbr,
    ExpirationDate = g.Key.ExpirationDate
}

LINQPad and Linqer Output

AuthId  RmCnt   AuthType    ExpirationDate
30      0       Type1       12/31/2020
31      0       Type2       12/31/2020

Problem

The issue is that this LINQ query works perfectly when it is run in both Linqer and LINQPad. The output is exactly as desired. However, when it is run in C# with ASP.NET, MVC, and Entity Framework, the RmCnt will never be 0. Instead, it will be 1. No matter what is changed that seems to be the case. One other user asked a similar question but I can't figure out how to apply their solution with my query.

Any help would be appreciated.

Are you sure the output of the query is 1 and that is not an output Status Code from the code execution? — dfundako, Oct 11 '18 at 17:41
Yes, I'm sure. I have checked it both by printing it to the actual page as well as looking through the results in debug mode of Visual Studio. — Michael Ziluck, Oct 11 '18 at 18:04

score 0 · Answer 1 · answered Oct 11 '18 at 18:09

I figured out the problem

Problem

I am still not sure what the root cause of the problem is as the LINQ Query I had originally worked perfectly in LINQPad and Linqer but not in the actual code.

However, one of the things I changed from the original output of Linqer was changing p.ra.AuthTypeId != null to p.ra.AuthTypeId != 0 because Visual Studio was complaining that AuthTypeId can never be null as it is an int rather than a Nullable<int>.

Solution

It was actually a rather simple change. p.ra.AuthTypeId != 0 needed to be changed to p.ra != null. This solution makes Visual Studio not complain about nullable types but still maintains the same output.

Full Working LINQ Query

from ua in db.FooBar_UserAuth
join ra in db.FooBar_RoomAuth
    on new { ua.FooBar_Authorization.AuthId, IsActive = true }
    equals new { ra.AuthId, ra.IsActive } into raJoin
from ra in raJoin.DefaultIfEmpty()
join ri in db.Room_Inventory
    on new { ra.RmId, IsActive = true }
    equals new { RmId = ri.RMID, ri.IsActive } into riJoin
from ri in riJoin.DefaultIfEmpty()
where
    ua.FooBar_Authorization.IsActive &&
    ua.IsPi == true &&
    ua.FooBar_User.IsActive &&
    ua.FooBar_User.UserId == 10
group new { ua.FooBar_Authorization, ua.FooBar_Authorization.FooBar_AuthType, ra } by new
{
    AuthId = (int?)ua.FooBar_Authorization.AuthId,
    ua.FooBar_Authorization.FooBar_AuthType.AuthTypeAbbr,
    ua.FooBar_Authorization.ExpirationDate
} into g
select new
{
    AuthId = g.Key.AuthId.Value,
    RmCnt = g.Count(p => p.ra != null),
    AuthType = g.Key.AuthTypeAbbr,
    ExpirationDate = g.Key.ExpirationDate
}

Linq Count() will always return 1 within Visual Studio

1 Answers1