Linq code doesn't return correct record

Question

I have a table named dbo.EmployeeType with three records:

PK_EmployeetypeID    EmployeeTypeName 
1                    Project Manager 
2                    Business Analyst 
3                    Developer

I have this piece of Linq code:

public static string GetTypeByID(int id)
{
    using (ProjectTrackingEntities1 db = new ProjectTrackingEntities1())
    {
        var type = db.EmployeeTypes.Select(o => new LOOKUPEmployeeType
        {
            PK_EmployeeTypeID = id,
            EmployeeTypeName = o.EmployeeTypeName
        });

        return type.FirstOrDefault().EmployeeTypeName;
    }
}

No matter what id I send to it, it returns Project Manager, and I'm confused as to why.

Answer 1

You need to apply a filter, otherwise you're just returning the first record and hard coding the ID. Try this:

public static string GetTypeByID(int id)
{
    using (ProjectTrackingEntities1 db = new ProjectTrackingEntities1())
    {
        //Here we apply a filter, the lambda here is what creates the WHERE clause
        var type = db.EmployeeTypes
            .FirstOrDefault(et => et.PK_EmployeeTypeID == id);

        if(type != null)
        {
            return type.EmployeeTypeName;
        }
        else
        {
            return "";
        }
    }
}

Note that using FirstOrDefault means if there are no matches, or multiple matches, type will be null and you will get an empty string returned.

Answer 2

Set a breakpoint on type = ... and inspect it. You have no Where in there so you get all - and Select just makes LOOKUPEmployeeTypes out of all of them.

FirstOrDefault then returns the first of those 3 which is always the ProjManager

Fix:

var type = db
    .EmployeeTypes
    .Where( o => o.Id == id)
    .Select(o => new LOOKUPEmployeeType
{
    PK_EmployeeTypeID = id,
    EmployeeTypeName = o.EmployeeTypeName
});