I want to show the most laatest product please check this Linq Query?

Question

   public List<Advertisement> GetLastetAdvertisement()
    {
        DemoContext _context = new DemoContext();
        using (_context)
        {
            return (from adv in _context.Advertisements
                    .Include(a => a.SubCatagory.Catagory)
                    .Include(a => a.City.Province.Country)
                    .Include(a => a.User)
                    .Include(a => a.Images)
                    .Include(a => a.Status)
                    .Include(a => a.Type)
                    .OrderBy(x => x.PostedOn)

                    select adv
                    ).Take(12).ToList();
        }
    }

This is the method that i have written . in this method i want to show the most latest advertisement in the page

What is the problem ? Do you have errors ? – Mihai Alexandru-Ionut Jan 17 '18 at 07:19 — Mihai Alexandru-Ionut, Jan 17 '18 at 07:19

score 1 · Answer 1 · answered Jan 17 '18 at 07:53

One of the slower parts of a database query is the transfer from the fetched data to your local machine.

Quite often people tend to transfer more data then they will use. Your tend to do the same. For example, your advertisement class probably has several foreign keys to User, Status, Type, etc. while User, Status, Type have a primary key with the same value that you also transfer. Besides I doubt that you will be displaying all these foreign keys.

You say you want to show the Latest Product your query returns the twelve newest product. If you really only want the Latest one, use FirstOrDefault.

Assuming you want the newest few items, OrderByDescending and Take will do the trick.

You mixed method syntax with query syntax. Method syntax is quite often used by people who think in Collections, in C# programming language. Besides method syntax allows some functionality that query syntax has not. Query syntax is an adaptation of C# for people who think more in SQL queries. It is easier for people to understand what your code does, and thus easier to test, debug and maintain if you stick to one of these two syntaxes. See Query syntax vs Method syntax

Your query will probably do what you want, but with the mentioned improvements I'd go for this:

var result = _context.Advertisements    // from all Advertisements
    .OrderByDescending(advertisement => advertisement.PostedOn)
                                        // order them by descending PostedOn         
    .Select(advertisement => new        // from every result element make a new item
    {                                   // containing the following properties
        // take only the Advertisement properties you plan to use
        Id = advertisement.Id,
        PostedOn = advertiesment.PostedOn,
        ...

        // Only if you plan to use properties of SubCategory:
        SubCategory = new
        {
             // again: take only the properties you plan to use:
             Name = advertisement.SubCategory.Name,
             Type = advertisement.SubCategpru.Type,
             ...
             // one of the items you wont use is the foreign key to Advertisements
        }

        // If you want you can return your items in a different structure
        // for instance, convert your City/Province/Country into an address:
        Address = new
        {
            City = advertisment.City.Name,
            Province = advertisement.City.Province.Name,
            State = advertisement.City.Province.State.Name,
        }

        // etc for the other properties
    })
    // Select will keep the original oder
    // take only the first twelve of them
    .Take(12)
    // only do the ToList() if you know this is the end result
    .ToList();

I ordered before the Select, to make it possible to omit PostedOn if you didn't need it anymore after the query.

Great answer! Do you see a problem in mixing the two syntaxes? I nearly always mix them, because - what you also said - one lacks features of the other. — schlonzo, Jan 17 '18 at 09:32
One of the answers proposed to do a Select(x=>x), which wouldn't be necessary if you'd stick to one syntax. By the way, joining three or more tables is better understandable in query syntax. Method syntax looks horrible if you try to do this. Luckily when using entity framework I seldom need to do a join. I use the ICollections — Harald Coppoolse, Jan 17 '18 at 12:09
In the mentioned answer the `Select(x=>x)` just is not needed. If you remove it, the query works anyway... — schlonzo, Jan 17 '18 at 13:07

score 0 · Answer 2 · answered Jan 17 '18 at 07:26

Try the following updated code

public List<Advertisement> GetLastetAdvertisement()
{
    DemoContext _context = new DemoContext();
    using (_context)
    {
        return (from adv in _context.Advertisements
                .Include(a => a.SubCatagory.Catagory)
                .Include(a => a.City.Province.Country)
                .Include(a => a.User)
                .Include(a => a.Images)
                .Include(a => a.Status)
                .Include(a => a.Type)
                .OrderByDescending(x => x.PostedOn)
                .Select(x => x).Take(12).ToList();
    }
}

I want to show the most laatest product please check this Linq Query?

2 Answers2