Join and order in LINQ C#

Question

I have been given the following skeleton code:

var test = SqlCompact(
"OrderID", "ASC", 5, 2,
 out count, out sortCriteria, out sql);

This calls the SqlCompact method which performs joins of tables orders, employees and customers & then orders by the inputs e.g. "ASC" and "Column Name".

I have got the join query working but not sure how to order the results according to the input. This is my code for SqlCompact Method:

public List<MyJoin> SqlCompact(
    string sort, string sortDir, int rowsPerPage, int page, 
    out int count, out string sortCriteria, out string sql) {    

var cec = new SqlCeConnection(
    string.Format(@"Data Source={0}\Northwind.sdf", Path));
var nwd = new DataContext(cec);

nwd.Log = new StringWriter();
var orders = nwd.GetTable<Order>();
var employees = nwd.GetTable<Employee>(); //
var customers = nwd.GetTable<Customer>(); //

count = orders.Count();
sortCriteria = "";

 var q = (from od in orders
        join em in employees on od.EmployeeID equals em.EmployeeID
        join ct in customers on od.CustomerID equals ct.CustomerID
        //orderby em.EmployeeID
        select new
        {
            od.OrderID,
            od.ShipCountry,
            ct.CompanyName,
            ct.ContactName,
            FullName = em.FirstName + ' '+ em.LastName, 
        }).ToList();

q.Dump();

sortCriteria: a string composed from sort and sortDir – in the format directly usable by Dynamic LINQ, e.g.

OrderID ASC 
ContactName DESC, OrderID DESC

what is `sortCriteria`? I think you're going to need to provide more information — user1666620, Oct 12 '15 at 09:56
look at the following link http://stackoverflow.com/questions/7265186/how-do-i-specify-the-linq-orderby-argument-dynamically — Rajeev Kumar, Oct 12 '15 at 10:11

score 0 · Answer 1 · answered Oct 12 '15 at 10:10

0

This is what you want to do: if (sort == "OrderId") { q = q.OrderBy(x => x.OrderId); }

Passing in the the column name as a string is not a great way to do it though.

answered Oct 12 '15 at 10:10

serial8

60
6

I'm getting this error: Cannot implicitly convert type 'System.Linq.IOrderedEnumerable' to 'System.Collections.Generic.List' – Landon Oct 13 '15 at 00:00
To avoid that error, just call ToList() eg ... `q = q.OrderBy(x => x.OrderId).ToList();` – sgmoore Oct 14 '15 at 09:11
Quick note use q.OrderByDescending(x => x.OrderId) to flip the sort order – serial8 Oct 14 '15 at 10:55

Join and order in LINQ C#

1 Answers1