在linq查询中使用case语句 [英] Using case statement in linq query

问题描述

我有一个这样的linq查询：

I have a linq query like this :

 var trfplanList = (from at in entities.tdp_ProviderAccomodationType
                        join ap in entities.tdp_ProviderAccomodationTariffPlan on at.PATID equals ap.FK_PATID
                        join ac in entities.tdp_ProviderAccomodationCategory on ap.FK_PACID equals ac.PACID
                        where at.FK_ProviderID == CityID && at.IsApproved == 0 && ap.IsApproved == 0 && ac.AccomodationCategory == "Double Occupy"
                        orderby at.AccomodationType,ap.FromDate,ap.SType 
                        select new AccomodationTariff
                        {
                            AccomodationType = at.AccomodationType,
                            SType = ap.SType,
                            FromDate = Convert.ToDateTime(ap.FromDate),
                            ToDate = Convert.ToDateTime(ap.ToDate),
                            RoomTariff = Convert.ToDecimal(ap.Rate),
                            ExPAXRate = Convert.ToDecimal(at.PerPaxRate)
                        }).ToList();

我有两个问题：

1. 在select new {}块中分配时，无法转换值吗？在项目中给我一个错误。

1. Can't I convert value while assigning in the select new {} block ? it is giving me an error in project.

我想使用'case'，而选择 ExPAXRate 数据库例如在SQL中我用来写：

I want use 'case' while selecting ExPAXRate from the database for example in SQL I used to write :

CASE ap.SType WHEN'Off Season'THEN at.PerPaxRateOS ELSE at.PerPaxRate END AS ExPAXRate

CASE ap.SType WHEN 'Off Season' THEN at.PerPaxRateOS ELSE at.PerPaxRate END AS ExPAXRate

我可以在linq查询中使用这样的东西吗？

Can I use something like this in linq query ?

推荐答案

在select new {}块中分配时无法转换值

Can't I convert value while assigning in the select new {} block

不，你不能（可悲的是）。 EF不知道如何将其翻译成SQL。

No, you can't (sadly). EF doesn't know how to translate it into SQL.

我想使用'case'

I want use 'case'

您可以使用三元运算符（？）：

You can use the ternary operator (?):

ExPAXRate = at.OffSeason ? at.PerPaxRateOS : at.PerPaxRate

（假设 at.OffSeason 存在）。

(assuming that at.OffSeason exists).

转换问题的解决方案可能是首先投射到匿名类型，然后在内存中调用为 AccomodationTariff ：

A solution for the conversion issue could be to project into an anonymous type first and then, in memory, to AccomodationTariff:

...
select new
{
    AccomodationType = at.AccomodationType,
    SType = ap.SType,
    FromDate = ap.FromDate,
    ToDate = ap.ToDate,
    RoomTariff = ap.Rate,
    ExPAXRate = at.PerPaxRate
}).AsEnumerable()
.Select(x => new AccomodationTariff
{
    AccomodationType = x.AccomodationType,
    SType = x.SType,
    FromDate = Convert.ToDateTime(x.FromDate),
    ToDate = Convert.ToDateTime(x.ToDate),
    RoomTariff = Convert.ToDecimal(x.Rate),
    ExPAXRate = Convert.ToDecimal(x.PerPaxRate)
}).ToList();

这篇关于在linq查询中使用case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！