Geek asks and answers

Linq for orderby entities

How to convert this query in linq to objects with an entity:

SELECT  customer_id,
    customer_name,
    customer_code
FROM dbo.V_STF_CUSTOMER
WHERE   company_id=@company_id AND
ORDER BY    CASE
            WHEN ISNUMERIC(customer_name)=1 THEN 1
            ELSE 0
        END,
        customer_name

I have it:

return CUstomers.GetQuery().
            Where(x => x.CompanyId == companyId).
        OrderBy(??This is my problem??);

I do not know how to transfer an order. Any idea?

+3
source share
3 answers
return Customers.GetQuery().
            Where(x => x.CompanyId == companyId).
        OrderBy(x=> SqlFunctions.IsNumeric(x.customer_name)).
        ThenBy(x=> x.customer_name);
+7
source

You can use SqlFunctions.IsNumeric in your query to map to IsNumeric on the Sql server.

Something along the lines of:

        var results = from c in customers
                      where c.companyId = companyId
                      orderby SqlFunctions.IsNumeric(c.customerName) == 1 ? 1 : 0, c.customerName
                      select new { c.customerId, c.customerName, c.customerCode };
0
source

At first, I think something is wrong with the SQL query in

WHERE   company_id=@company_id AND

why did you add AND?

you can achieve ISNUMERIC only in Entity Framewwork (not linq to sql) using SqlFunctions.IsNumeric

return CUstomers.GetQuery().
            Where(x => x.CompanyId == companyId).
            OrderBy(x => SqlFunctions.IsNumeric(x.customer_name)).
            ThenBy(x => x.customer_name);
0
source

More articles:


All Articles