Differences between a linq query and extension In a MVC 4 application

Question

I have a controller that is linking 3 tables together I understand how to make it present on web grid but am having trouble with some functionality of the web grid and the differences between a linq query and extension In a MVC 4 Basic application.

Here are the requirements of the controller:

When displaying such a page, the Controller must receive the following MVC parameters:

• page: page number, starting with 1, default 1
• rowsPerPage: rows per page (page size), default 10
• sort: name of sort column, default ProductID
• sortDir: sort direction, ASC or DESC, default ASC

Furthermore for Sorting: initially on ProductID ASC; then, clicking on a column header sorts primarily on that column, alternating ASC and DESC; followed by a secondary sort on ProductID ASC (to unambiguously break ties, if any)

Here is my code I can get the linked table to display but I do not know how to make be sorted per column etc.

Relevant code in Controller:

 string SwdConStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\southwnd.mdf;Integrated Security=True;Connect Timeout=30";

public ActionResult WebGrid_SQL(string sort = "ProductID", string sortDir = "ASC", int rowsPerPage = 10, int page = 1)
    {
        var swd = new Southwind(SwdConStr);

        var sortCriteria = // should do better
            string.Format("{0} {1}, ProductID {1}", sort, sortDir);

        var res =
            from p in swd.Products
            join c in swd.Categories on p.CategoryID equals c.CategoryID
            join s in swd.Suppliers on c.CategoryID equals s.SupplierID

            orderby (sortCriteria)
            //.Skip((page - 1) * rowsPerPage)
            //  .Take(rowsPerPage)
            .Select(m => AS3.Models.MyModel
            {
                ProductID = p.ProductID,
                ProductName = p.ProductName,
                CategoryName = c.CategoryName,
                CompanyName = s.CompanyName,
                ContactName = s.ContactName,
                Country = s.Country
            }).ToList();

        ViewBag.sort = sort;
        //ViewBag.sortDir = sortDir;
        //ViewBag.rowsPerPage = rowsPerPage;
        //ViewBag.page = page;
        ViewBag.count = swd.Products.Count();

        return View(res);
    }

Please note with some commenting out the aobove code displays the correct table on the webpage however it has no sorting capabilities etc.

The above code has two errors both at the semicolon after the ToList

• Error 1 A query body must end with a select clause or a group clause
• Error 2 Cannot convert lambda expression to type 'string' because it is not a delegate type

Also in-case anyone needs to see the View to solve the problem:

Relevant code in View:

 @model IEnumerable<AS3.Models.MyModel>

 @{
 ViewBag.Title = "WebGrid SQL";
 }

 @{  // copy local = true
 var grid = new System.Web.Helpers.WebGrid(null,
    defaultSort: ViewBag.sort);
    //rowsPerPage: ViewBag.rowsPerPage);

 grid.Bind(Model, rowCount: ViewBag.count, autoSortAndPage: false);
 }

<h2>WebGrid SQL</h2>

<div>
    @grid.GetHtml(
       columns: grid.Columns(
        grid.Column("ProductID", "Product ID", canSort: true),
        grid.Column("ProductName", "Product Name", canSort: true),
        grid.Column("CategoryName", "Category Name", canSort: true),
        grid.Column("CompanyName", "Company Name", canSort: true),
        grid.Column("ContactName", "Contact Name", canSort: true),
        grid.Column("Country", "Country", canSort: true)
      )
  )

      <h3>Ordering by:</h3>
     <div>@grid.SortColumn @grid.SortDirection</div>
  </div>

Answer 1

Error 1 A query body must end with a select clause or a group clause

In query syntax, a LINQ statement should always end with a select clause. So that's the first thing to do:

var res = from p in swd.Products
          join c in swd.Categories on p.CategoryID equals c.CategoryID
          join s in swd.Suppliers on c.CategoryID equals s.SupplierID
          orderby (sortCriteria)
          select ...

Note that this is not the same as...

(from p in swd.Products
  join c in swd.Categories on p.CategoryID equals c.CategoryID
  join s in swd.Suppliers on c.CategoryID equals s.SupplierID
  orderby (sortCriteria))
.Select(x => ....)

...because the part between the parentheses is still a "query body" begging for a select clause.

Error 2 Cannot convert lambda expression to type 'string' because it is not a delegate type

That's the kind of error that occurs in many cases that something is wrong with the lambda expression. One common cause is that there's no using System.Linq. But there's one obvious flaw in your statement: sortCriteria is just a string, but you should enter property names there, like...

var res = from p in swd.Products
          join c in swd.Categories on p.CategoryID equals c.CategoryID
          join s in swd.Suppliers on c.CategoryID equals s.SupplierID
          orderby p.ProductID
          select ...

Finally, there's no new keyword for creating a AS3.Models.MyModel. So the final statement is:

var res = (from p in swd.Products
          join c in swd.Categories on p.CategoryID equals c.CategoryID
          join s in swd.Suppliers on c.CategoryID equals s.SupplierID
          orderby p.ProductID
          select new AS3.Models.MyModel
          {
              ProductID = p.ProductID,
              ProductName = p.ProductName,
              CategoryName = c.CategoryName,
              CompanyName = s.CompanyName,
              ContactName = s.ContactName,
              Country = s.Country
          }).ToList();

Now this doesn't allow for a dynamic sort. If you want that you could take a look at this one: Dynamic LINQ OrderBy on IEnumerable<T>. This would allow you to add something like .OrderByDescending("ProductID") to your statement. (Before the ToList()).