C# / .Net 4.6.1 / Visual Studio 2017
I am having issues with an MS SQL query. The query joins in several tables across two databases (on the same server). The result goes into a list of "object". The exception I get is:
The specified cast from a materialized 'System.Int32' type to the 'System.Int64' type is not valid.
I have seen other SO questions on this and similar errors but I am not seeing the problem here. personId is a long. All of the various ...Id fields are bigint in the DB and long in the model.
The query is:
try {
var personId = GetPersonId(netid);
var personIdParam = new SqlParameter {
Value = personId,
DbType = DbType.Int64,
ParameterName = "PERSONID"
};
using (var pdb = new PersonnelContext()) {
var foo = pdb.Database.SqlQuery<PersonToSpaceInfo>(@"
SELECT
'Member',
sGroup.GroupId,
sGroup.PersonnelGroupId,
sGroup.Name,
Space.SpaceId,
Space.RoomId,
SiteSpaceUse.TypeDescription
FROM
Personnel.Personnel.Memberships AS Memberships
INNER JOIN Space.dbo.Person AS Person
ON Memberships.PersonId = Person.PersonnelPersonId
AND Person.PersonnelPersonId = @PERSONID
INNER JOIN Space.dbo.[Group] AS sGroup
ON Memberships.GroupId = sGroup.PersonnelGroupId
INNER JOIN Space.dbo.GroupList AS GroupList
ON sGroup.GroupId = GroupList.GroupId
INNER JOIN Space.dbo.Space AS Space
ON GroupList.SpaceId = Space.SpaceId
AND Space.IsActive = 1
INNER JOIN Space.dbo.SiteSpaceUse AS SiteSpaceUse
ON Space.SpaceId = SiteSpaceUse.SpaceId
WHERE GETDATE() BETWEEN Memberships.StartDate
AND ISNULL(Memberships.EndDate, GETDATE())
",
personIdParam).ToList();
}
} catch (Exception e) {
throw new Exception("get group list failed: " + e.Message);
}
The result object is:
public class PersonToSpaceInfo {
public string PersonType { get; set; }
public long GroupId { get; set; }
public long PersonnelGroupId { get; set; }
public string GroupName { get; set; }
public long SpaceId { get; set; }
public string RoomId { get; set; }
public string TypeDescription { get; set; }
}
Stacktrace:
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
at lambda_method(Closure , Shaper )
at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at MyProject.Controllers.HomeController.GetUserActiveGroups(String user) in C:\Users\me\Projects\MyProject\MyProject\Controllers\HomeController.cs:line 560
Edit
Line 560 in the stacktrace is the var foo = pdb.Database... line above. GetPersonId() returns long.
The models were created using the code-first interface. The following are model snippets requested by @mjwills
GroupList.GroupId / GroupList.SpaceId
public partial class GroupList {
public long GroupId { get; set; }
public long SpaceId { get; set; }
...
}
Memberships.GroupId / Memberships.PersonId
public partial class Membership {
public long PersonId { get; set; }
public long GroupId { get; set; }
...
}
Person.PersonnelPersonId
public partial class Person {
public long PersonId { get; set; }
public long PersonnelPersonId { get; set; }
...
}
SiteSpaceUse.SpaceId
public partial class SiteSpaceUse {
public long SpaceId { get; set; }
...
}
Space.SpaceId
public partial class Space
public long SpaceId { get; set; }
public long BuildingId { get; set; }
...
}
sGroup.GroupId / sGroup.PersonnelGroupId
public partial class Group {
public long GroupId { get; set; }
public long PersonnelGroupId { get; set; }
...
}
Edit 2
Just tried replacing the SqlParameter in the query with a valid three digit number and i still get the error. The query works in SQL Server Management Studio. I'll delete the model and connection string info and recreate to see what happens.
What happens is noting changes...
