linq to entities orderby

Question

如何将此查询转换为linq to entities with entity framework：

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

我有这个：

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

我不知道如何翻译订单。有什么想法吗？

Answer 1

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

Answer 2

可以在查询中使用SqlFunctions.IsNumeric来映射到Sql Server中的IsNumeric。

大致是这样的：

        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 };

Answer 3

首先，我认为SQL查询有问题。

WHERE   company_id=@company_id AND

为什么要添加AND？

通过使用SqlFunctions.IsNumeric，只能在实体框架(而不是linq to sql)中实现ISNUMERIC

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