Linq Query Question

Question

I have this code that returns a caseID from an Alleged Perpetrator table. This table also has a column "LastName". I want to search on caseID and return LastName but I don't know how to code it. I've been on the microsoft site looking for LINQ to SQL examples but still can't figure it out. Any help would be greatly appreciated!

Ken

public static class AllegedPerpetratorRepository
{
    public static IQueryable<AllegedPerpetrator> GetByCaseID(
        this IQueryable<AllegedPerpetrator> source,
        int caseID)
    {
        return (from s in source where s.CaseID.Equals(caseID) select s); 
    }
}

Answer 1

The very end should be:

. . . select s.LastName);

Edit:

Ahmed's suggestion and Jeroen's fix:

public static class AllegedPerpetratorRepository
{
    public static IEnumerable<string> GetByCaseID(
        this IQueryable<AllegedPerpetrator> source,
        int caseID)
    {
        return (from s in source where s.CaseID.Equals(caseID) select s.LastName); 
    }
}

Answer 2

Have you created a LINQ to SQL class from your database using the Visual Studio mapper tool?

You can add a 'new item' and then add a LINQ to SQL class based on your database schema. The tool will generate the classes from the tables for you.

Then you can use these classes which represent your database columns and tables (one class per table) to use LINQ.

There's some good tutorials on LINQ to SQL if you google it.

Answer 3

var perps = dataContext.AllegedPerpetrator.Where(p=>p.CaseID == caseIdValue)
            .Select(p=>p.LastName)

CaseIdValue is what you pass in=

Answer 4

If there is one and only one record for the given case id you can use the Single expression

var lastName = dataContext.AllegedPerpetrator.SingleOrDefault(i => i.CaseID == caseId).LastName