CODEWITHSUNDEEP
c#asp.net-mvclinqentity-framework-6
inżynier umair
inżynier umair

Reputation: 21

how to select records from multiple table with max count value from one table using Linq in Asp.net MVC C#

I have 3 tables: 1. Tbl_Model, 2. Tbl_ModelImg, 3. Tbl_Category

I want to know how to fetch the records from these 3 tables using categoryId.

The single model may have multiple images, but I want to show all the products of that category with their images. The problem is I only want a single image in that view. So when a user clicks on that particular model, the details of that model and all its images will show on the next view.

The following query is working fine but it displays all the images with their model name. Means If a model has 4 images than on category details page it displays 4 items with the same name and different images.

Here is the model class :

     public class showdata
       {
        public Tbl_ModelImg tmi { get; set; }
        public Tbl_Model tm { get; set; }
        public Tbl_SubCategory tblsubcategory { get; set; }
        }
    
       public ActionResult Categorydetails(string sid)
            {
            var sId = Int64.Parse(new 
         StandardModule().Decrypt(HttpUtility.UrlDecode(sid.ToString())));


            try
            {
                var query = (from c in db.Tbl_Model

                             join o in db.Tbl_ModelImg
                              on c.Model_Id equals o.Model_Id 
                             join d in db.Tbl_SubCategory on c.SubCategory_Id 
                             equals d.Id
                             where c.SubCategory_Id == sId
                             select new showdata()
                             {
                                 tm = c,
                                 tmi = o,
                                 tblsubcategory = d                  


                             }).OrderByDescending(d => d.tm.Id).ToList();


                    return View(query);
                }

Upvotes: 0

Views: 672

Answers (2)

Jaquez
Jaquez

Reputation: 920

Replace the second line of the LINQ query with

join o in db.Tbl_ModelImg.GroupBy(m => m.Model_Id).Select(m => m.First())

The GroupBy() will group the items in Tbl_ModelImg by Model_Id and then the Select() will cut the contents of the resulting dataset down to just the first item in each group.

Once the where clause restricts your results to just those that items that join to the correct SubCategory_Id then you have what you need.

Upvotes: 0

inżynier umair
inżynier umair

Reputation: 21

                var query = (from c in db.Tbl_Model

                         join o in db.Tbl_ModelImg.GroupBy(m => m.Model_Id).Select(m 
                          => m.FirstOrDefault())
                          on c.Model_Id equals o.Model_Id 
                         join d in db.Tbl_SubCategory on c.SubCategory_Id equals d.Id
                         where c.SubCategory_Id == sId
                         select new showdata()
                         {
                             tm = c,
                             tmi = o,
                             tblsubcategory = d


                         }).OrderByDescending(d => d.tm.Id).ToList();

Upvotes: 0

Related Questions