1

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);
    }
}

1 Answer 1

3

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

sGroup.Name as [GroupName]
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.