CODEWITHSUNDEEP
sqllinqasp.net-mvc-4linq-to-sql
McKeymayker
McKeymayker

Reputation: 358

Select the first id from table

I want to list all the entries depending on my subcategory. So i want to display the entries list on load home page without previous selecting any categories or subcategories.

I've made a query that is returning list of entries, but putting manually subCategoryId.

Here is the query:

 int userId = WebSecurity.GetUserId(User.Identity.Name);

  var preQuery = (from sub in dc.SubCategory
                  join c in dc.Category on sub.SubCategoryId equals c.Id
                  join u in dc.User on c.UserId equals u.UserId
                  where u.UserId == userId
                  select sub.SubCategoryId).Take(1).FirstOrDefault();

        var query = (from e in dc.Entry
                     join sub in dc.SubCategory on e.SubCategoryId equals sub.SubCategoryId
                     join cat in dc.Category on sub.CategoryId equals cat.Id
                     join u in dc.User on cat.UserId equals u.UserId
                     where ((u.UserId == userId) && (cat.UserId == userId) 
                         && (sub.CategoryId == cat.Id) && (e.SubCategoryId == preQuery ))
                     select e).ToList();

        return View(query);

So my point is that i want instead of writing manually id of 6, want to take the list of subcategories ids and select the first id from it. I've made prequery but still not the required result.Any suggestions what i am doing wrong?

Upvotes: 0

Views: 128

Answers (1)

McKeymayker
McKeymayker

Reputation: 358

I was able to get a solution for my question, so i am posting the solution. If anyone has a suggestion of optimizing the solution, is welcome to share.

  int userId = WebSecurity.GetUserId(User.Identity.Name);

        var preQuery = (from e in dc.Entry
                     join sub in dc.SubCategory on e.SubCategoryId equals sub.SubCategoryId
                     join cat in dc.Category on sub.CategoryId equals cat.Id
                     join u in dc.User on cat.UserId equals u.UserId
                     where u.UserId == userId
                     select e.SubCategoryId).Take(1);

        var query = (from e in dc.Entry
                     join sub in dc.SubCategory on e.SubCategoryId equals sub.SubCategoryId
                     join cat in dc.Category on sub.CategoryId equals cat.Id
                     join u in dc.User on cat.UserId equals u.UserId
                     where ((u.UserId == userId) && (cat.UserId == userId)
                         && (sub.CategoryId == cat.Id) && (e.SubCategoryId == preQuery.FirstOrDefault()))
                     select e).ToList();

        return View(query);

Upvotes: 1

Related Questions