none
Dynamic LINQ OrderBy in VB.NET Question RRS feed

  • Question

  • I have two datatables that I am using in a Linq query.  But I need to be able to sort by any field or combination of fields.  I have found numerous examples in C# but have been unable to modify them to VB. 

    Here is the code.  I would like to accomplish the sorting with Linq if possible. 

    Does anyone have any suggestions?

    Thanks, Tim

    Below is the code...

        Private Sub GetOrderForLabel_LINQ()

           
            Dim xlprices = ExcelDataSet.XLDataset.Tables(0).AsEnumerable()
            Dim oeorder = ExcelDataSet.OrderItemDataset.Tables(0).AsEnumerable

            Dim orderItems = From p In xlprices, o In oeorder _
                                   Where p!MfgItemNo.ToString.Trim = o!item_no.ToString.Trim And _
                                   p!MfgFinishNo.ToString.Trim = o!pick_seq.ToString.Trim _
                                   Select p!SKU, p!Description, p!Retail, p!MfgItemNo, p!MfgFinishNo, o!qty_ordered ').ToList

            'This line needs to the Dynamic OrderBy so it can be sorted by any field

            Dim qry = orderItems.OrderBy(Function(m) m.Retail)

            'Create datatable
            Dim oLabelData As New DataTable("LabelDate")
            oLabelData.Columns.Add("SKU", GetType(String))
            oLabelData.Columns.Add("Description", GetType(String))
            oLabelData.Columns.Add("Retail", GetType(Decimal))
            oLabelData.Columns.Add("MfgPart", GetType(String))
            oLabelData.Columns.Add("MfgFinish", GetType(String))
            oLabelData.Columns.Add("QtyOrd", GetType(Decimal))
            'add data to datatable
            For Each o In qry
                oLabelData.Rows.Add(New Object() {o.SKU, o.Description, o.Retail, o.MfgItemNo, o.MfgFinishNo, o.qty_ordered})
            Next

            ExcelDataSet.LabelDataTable = oLabelData

        End Sub


    Tim Conway

    Thursday, January 31, 2013 2:03 AM

Answers

  • Hi Tim,

    Welcome to the MSDN forum.

    I think you are required to check the parameter type to generated the different OrderBy query. Please check this simple sample:

                using (var context = new Entities())
                {
                    var query = (from p in context.CompanyProducts
                                 select p).ToList();
    
                    String s = "CompanyID";
                    IQueryable<CompanyProduct> queryableData = query.AsQueryable<CompanyProduct>();
                    var t = queryableData.ElementType.GetProperty(s).PropertyType.ToString();
    
    
                    switch (t)
                    {
                        case "System.Int32":
                            {
                                ParameterExpression pe = Expression.Parameter(typeof(CompanyProduct), "p");
                                MethodCallExpression orderByCallExpression = Expression.Call(
                                typeof(Queryable),
                                "OrderBy",
                                new Type[] { queryableData.ElementType, typeof(Int32) },
                                queryableData.Expression,
                                Expression.Lambda<Func<CompanyProduct, Int32>>(Expression.Property(pe, s), new ParameterExpression[] { pe }));
                                IQueryable<CompanyProduct> results = queryableData.Provider.CreateQuery<CompanyProduct>(orderByCallExpression);
                                foreach (CompanyProduct company in results)
                                    Console.WriteLine("{0} {1} {2}", company.ID, company.Name, company.CompanyID);
                                break;
                            }
                        //default:
                    }
                }
                Console.ReadLine();
    
    

        public partial class CompanyProduct
        {//Entity class definition
            public int ID { get; set; }
            public string Name { get; set; }
            public int CompanyID { get; set; }
        
            public virtual Company Company { get; set; }
        }
    

    You can refer to this page to convert the code to VB.NET: http://msdn.microsoft.com/en-us/library/bb882637.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by tconway Monday, February 4, 2013 1:03 PM
    Friday, February 1, 2013 7:40 AM

All replies

  • Hi Tim,

    Welcome to the MSDN forum.

    I think you are required to check the parameter type to generated the different OrderBy query. Please check this simple sample:

                using (var context = new Entities())
                {
                    var query = (from p in context.CompanyProducts
                                 select p).ToList();
    
                    String s = "CompanyID";
                    IQueryable<CompanyProduct> queryableData = query.AsQueryable<CompanyProduct>();
                    var t = queryableData.ElementType.GetProperty(s).PropertyType.ToString();
    
    
                    switch (t)
                    {
                        case "System.Int32":
                            {
                                ParameterExpression pe = Expression.Parameter(typeof(CompanyProduct), "p");
                                MethodCallExpression orderByCallExpression = Expression.Call(
                                typeof(Queryable),
                                "OrderBy",
                                new Type[] { queryableData.ElementType, typeof(Int32) },
                                queryableData.Expression,
                                Expression.Lambda<Func<CompanyProduct, Int32>>(Expression.Property(pe, s), new ParameterExpression[] { pe }));
                                IQueryable<CompanyProduct> results = queryableData.Provider.CreateQuery<CompanyProduct>(orderByCallExpression);
                                foreach (CompanyProduct company in results)
                                    Console.WriteLine("{0} {1} {2}", company.ID, company.Name, company.CompanyID);
                                break;
                            }
                        //default:
                    }
                }
                Console.ReadLine();
    
    

        public partial class CompanyProduct
        {//Entity class definition
            public int ID { get; set; }
            public string Name { get; set; }
            public int CompanyID { get; set; }
        
            public virtual Company Company { get; set; }
        }
    

    You can refer to this page to convert the code to VB.NET: http://msdn.microsoft.com/en-us/library/bb882637.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by tconway Monday, February 4, 2013 1:03 PM
    Friday, February 1, 2013 7:40 AM
  • This is what I needed.  But in the end, I went with sorting by the DataView from the Datatable and then exporting to a new table.  It turned out to be easier. 

    Thanks so much for your help on this.

    Tim


    Tim Conway

    Monday, February 4, 2013 1:05 PM