CODEWITHSUNDEEP
c#.netentity-framework
7 Reeds
7 Reeds

Reputation: 2539

C#, .Net raw SQL Server query returns numeric values but null strings

I am using VS2017, C#, EF 6.2.0. I have a query (shown below) that reaches into two different databases. One database holds employee info and the other has space usage data, read as: to which office(s), lab(s), etc does Person-X have access?

I can run the raw query in SSMS, replacing @PERSONID with a real personId, and it works. When I run the query via the method below, it returns a result, but the string GroupName is always null.

Do I have to do something special for strings?

public class PersonToMemberInfo 
{
    public long GroupId { get; set; }
    public long PersonnelGroupId { get; set; }
    public string GroupName { get; set; }
}

public List<PersonToMemberInfo> GetPersonMemberships(PersonnelContext pdb, long personId) {
    try {
        var personIdParam = new SqlParameter {
            Value = personId,
            DbType = DbType.Int64,
            ParameterName = "PERSONID"
        };

        var myList = pdb.Database.SqlQuery<PersonToMemberInfo>(@"
            SELECT DISTINCT
                sGroup.GroupId,
                sGroup.PersonnelGroupId,
                sGroup.Name
            FROM
                Company.Personnel.Memberships AS Memberships
            INNER JOIN 
                Space.dbo.Person AS Person ON Memberships.PersonId = Person.PersonnelPersonId
                                           AND Person.PersonnelPersonId = @PERSONID
                                           AND GETDATE() BETWEEN Person.StartDate AND isnull(Person.EndDate, GETDATE())
            INNER JOIN 
                Space.dbo.[Group] AS sGroup ON Memberships.GroupId = sGroup.PersonnelGroupId
            INNER JOIN  
                Space.dbo.GroupList AS GroupList ON sGroup.GroupId = GroupList.GroupId
                                                 AND GETDATE() BETWEEN GroupList.StartDate AND ISNULL(GroupList.EndDate, GETDATE())--
            WHERE  
                GETDATE() BETWEEN Memberships.StartDate AND ISNULL(Memberships.EndDate, GETDATE());",
            personIdParam).ToList();

        return myList;
    } catch (Exception e) {
        throw new Exception("GetPersonMemberships: " + e.Message);
    }
}

Upvotes: 1

Views: 172

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062640

The column name (currently Name) probably needs to match the member name (GroupName); try:

sGroup.Name as [GroupName]

Upvotes: 3

Related Questions