Linq语句不从Management Studio中的sql select复制数据 [英] Linq statement not reproducing data from sql select in Management Studio

问题描述

我正在尝试将以下查询从SQL转换为linq，但在.net中产生的结果集与在SQL Server Management Studio中产生的结果不同

I am trying to convert the following query from SQL to linq but it's not producing the same result set in .net as it is in SQL Server Management Studio

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 6 
    [ID], [customerInfo], [IVACODE], [Contribution],
    [DUE_DATE], [isActive], [isDeleted]
FROM 
    [portal].[dbo].[tblPortalContributionSchedule]
WHERE
    customerInfo = '01F6B68B-6FC2-4F9D-B586-6934B8D6C979'
    AND DUE_DATE <= '2016/09/26' 
    AND DUE_DATE <> '2016/09/26'
ORDER BY 
    DUE_DATE DESC

到目前为止，在我的linq中，我有以下比较:

In my linq I have so far the following comparison:

public List<tblPortalContributionSchedule> getUserContributions(Guid _customerInfoId,DateTime _date)
{
      List<tblPortalContributionSchedule> _contributions = 
             portalEntities.tblPortalContributionSchedules
                 .Where(a => a.customerInfo == _customerInfoId 
                    && a.isDeleted == false 
                    && a.DUE_DATE <= _date 
                    && a.DUE_DATE !=_date )
                 .Take(6)
                 .OrderByDescending(o=> o.DUE_DATE)
                 .ToList();

        return _contributions;
}

然后我的回叫电话

List<tblPortalContributionSchedule> _payments = _dal.getUserContributions(_customerId, Convert.ToDateTime("2016/09/26"));

这是我的数据的屏幕抓图，但未产生预期的结果

This is a screen grab of me data but its not producing the desired results

https://snag.gy/3uKCPh.jpg

我想知道是否有人可以帮助我将其正确转换为linq.

I was wondering if someone could help me in converting this properly to linq.

这是我应该得到的数据结果

This is the data result I should be getting

https://snag.gy/OQrql8.jpg

基本上我想得到最近的6笔付款；在7年内可能会有30笔付款，但是我只对基于当月的客户最近6笔付款感兴趣.

Basically I want to get the last 6 payments; there could be 30 payments spread over 7 years but I'm only interested in customer's last 6 payments based on the current month.

推荐答案

让我们解构一下:

portalEntities.tblPortalContributionSchedules
              .Where(a => a.customerInfo == _customerInfoId)
        1 ->  .Where(a => !a.isDeleted)
              .Where(a => a.DUE_DATE <= _date)
        2 ->  .Where(a => a.DUE_DATE != _date)
              .OrderByDescending(a => a.DUE_DATE)
        3 ->  .Take(6)
              .ToList();

1. 比== false
更具可读性
2. 与上面的语句冲突.如果要排除它，只需使用<.
3. 将放在之后OrderByDescending.

1. More readable than == false
2. Conflicts with statement above it. Just use < if you want to exclude it.
3. Place after OrderByDescending.

这篇关于Linq语句不从Management Studio中的sql select复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！