c# SQL - how get the most recent date of a customers last recent car service

Question

how do i get the most recent date of a customers last recent car service

The below pulls the data but not sure how to get just the most recent dates

//Search Database
            if (query.Any()) 
            {
                int carID = query.FirstOrDefault().Id;
                string carRegg = query.FirstOrDefault().regNo;
                string carMake = query.FirstOrDefault().Make;
                string carModel = query.FirstOrDefault().Model;

                var test = (from a in dbC.Cars
                            where a.Id == carID
                            join b in dbC.Services on a.Id equals b.CarId
                            join c in dbC.PartsUseds on b.ServiceWrkNo equals c.ServiceServiceWrkNo
                            join d in dbC.Parts on c.PartsPartNo equals d.PartNo
                            select new
                            {
                                serviceNum = b.ServiceWrkNo,
                                date = b.Date,
                                PartNo = c.PartsUsedNo,
                                replacedParts = d.PartName
                            }).ToList();
                Console.WriteLine();
                Console.WriteLine("- - - - - - - - - - - - - - - - - - - - - - - - - - - - ");
                Console.WriteLine("CAR SERVICE DETAILS: " + carRegg + " " + carMake + " " + carModel);
                Console.WriteLine("- - - - - - - - - - - - - - - - - - - - - - - - - - - - " + "\n");
                Console.WriteLine("ServiceNo \t DATE \t Items Replaced \t Cost");
                foreach (var item in test)
                {
                    float cost = item.PartNo + item.PartNo;
                    Console.WriteLine(item.serviceNum + "\t\t   " + item.date.ToShortDateString() + "\t\t  " + cost);
                }
            }

You could order by date descending, and only take the first record. — Nick, Oct 16 '17 at 20:13
If you group by customer you can then find the max service date. See for example https://stackoverflow.com/questions/157786/how-do-i-get-the-max-row-with-a-group-by-in-linq-query — Greg, Oct 16 '17 at 20:15
Do these tables have foreign keys defined? With LINQ, you can actually navigate instead of joining. Joining is required in SQL, but in LINQ it is only needed where the navigational properites (foreign keys) are missing. — Greg, Oct 16 '17 at 20:52

Ratatoskr · Answer 1 · 2017-10-16T20:17:33.263

1

var test = (from a in dbC.Cars
                        where a.Id == carID
                        join b in dbC.Services on a.Id equals b.CarId
                        join c in dbC.PartsUseds on b.ServiceWrkNo equals c.ServiceServiceWrkNo
                        join d in dbC.Parts on c.PartsPartNo equals d.PartNo
                        orderby b.Date descending
                        select new
                        {
                            serviceNum = b.ServiceWrkNo,
                            date = b.Date,
                            PartNo = c.PartsUsedNo,
                            replacedParts = d.PartName
                        }).ToList();

orderby b.Date descending to order the records by the date value with the most recent being the first.

.Take(5).ToList(); to only show the 5 most recent, as an example.

edited Oct 16 '17 at 20:17

answered Oct 16 '17 at 20:15

Ratatoskr

189
5

Thanks for the replay. looking for it just to populate only the most recent date if its possible? – Seamy20 Oct 16 '17 at 20:17
Change `.ToList()` to `.FirstOrDefault()` – Ratatoskr Oct 16 '17 at 20:18
1

You'll want to remove your `foreach` and replace it with logic that's just using the one entry as well. – Ratatoskr Oct 16 '17 at 20:20

score 0 · Accepted Answer · answered Oct 16 '17 at 20:40

Or you could try something like this - only returns data with the max service date

var test = (from a in dbC.Cars                            
            join b in dbC.Services on a.Id equals b.CarId
            join c in dbC.PartsUseds on b.ServiceWrkNo equals c.ServiceServiceWrkNo
            join d in dbC.Parts on c.PartsPartNo equals d.PartNo
            where a.Id == carID && b.Date == ((from b1 in dbC.Services where b1.CarId == b.CarId select b1.Date).Max())
            select new
            {
                serviceNum = b.ServiceWrkNo,
                date = b.Date,
                PartNo = c.PartsUsedNo,
                replacedParts = d.PartName
            }).FirstOrDefault();

Greg · Answer 3 · 2017-10-16T21:17:19.380

If we have navigational properties (already automatically created from the foreign keys), I would mention that you can do things like the psuedo-code below:

    var lastService = (from s in dbC.Services
                       where s.CarId = carID
                       orderby s.Date descending
                       select new
                       {
                           s.ServiceWrkNo,
                           s.Date,
                           ServiceParts = s.PartsUsed
                       }).FirstOrDefault();

This is to show the concepts of joins versus navigational properties.

c# SQL - how get the most recent date of a customers last recent car service

3 Answers3