Linq-sql classes to select using max

Question

i have a table Employee it contain fields ename,eid,workid,date,work etc.. which store the work assigned to an employee with workid as primarykey, so same enames will be there ,so i need to rerieve datas where ename=a particular name and workid for that must be maximum....

please help me

thanks

Answer 1

I would strongly suggest that you separate work and employees into separate tables so that you don't duplicate employee details for every work item you have.

So on the employee table you'd have - ename, eid (primary key)

and on the work table you'd have - workid (primary key), date, work, eid (foreign key to employee.eid)

The above will allow you to have multiple work items for each employee, and you wont have to have duplicate employee details (like name and id) for each work item.

Anyway, to answer your question, you can do the following -

var e = (from em in DataContext.Employees
               where em.ename = "John Smith"
               Select em).Max(x => x.workid);

However, you'd be far better off doing what i suggest, so your query to retrieve an employee could look like this instead -

var e = from em in DataContext.Employees
        where em.name = "John Smith"
        Select em;