none
Embedded linq query fails RRS feed

  • Question

  • Being new to Linq, I have converted the following SQL using Linqer followed by the resulting Linq statement.

    SELECT  [NAME_SENDER],
    [NAME_RECEIVER],
                    [TOTAL_RECEIVER] , -- total amount sent
                    CASE [ID_FLAG_RECEIVER]
                      WHEN 'A' THEN 'VOID'
                      WHEN 'C' THEN 'CANCEL'
                      WHEN 'E' THEN 'EXPIRED'
                      WHEN 'H'
                      THEN ( SELECT CASE WHEN EXISTS ( SELECT   1
                                                       FROM     [dba].[MOTIVO_HOLD] MH
                                                                WITH ( NOLOCK )
                                                       WHERE    MH.ID_BRANCH = R.ID_BRANCH
                                                                AND MH.ID_RECEIVER = R.ID_RECEIVER
                                                                AND MH.TYPE_MOTIVO_HOLD = 70 )
                                         THEN 'STAGED'
                                         ELSE 'HOLD'
                                    END
                           )
                      WHEN 'I' THEN 'DONE'
                      WHEN 'P' THEN 'PAID'
                      WHEN 'T' THEN 'TRANSFERED'
                    END AS TransactionStatus ,
                    [DATE_RECEIVER],
                    R.[ID_BRANCH] ,
                    R.[ID_RECEIVER] 
            FROM    [dba].[RECEIVER] R
                    INNER JOIN dba.SENDER S ON s.ID_BRANCH = r.ID_BRANCH
                                               AND s.id_sender = r.ID_SENDER
            WHERE   R.[ID_BRANCH] = @branchId
                    AND DATE_RECEIVER BETWEEN @startDate AND GETDATE()
            ORDER BY DATE_RECEIVER DESC

    Here is the linq statement that fails:

    var models =
                    from r in _context.Receivers
                    join s in _context.Senders
                        on new { r.ID_SENDER, r.ID_BRANCH }
                        equals new { s.ID_SENDER, s.ID_BRANCH }
                    where
                        r.ID_BRANCH == branchId &&
                        r.DATE_RECEIVER >=
                        (DateTime)SqlFunctions.DateAdd("day", -3, SqlFunctions.GetDate()) &&
                        r.DATE_RECEIVER <= SqlFunctions.GetDate()
                    orderby
                        r.DATE_RECEIVER descending
                    select new
                    {
                        s.NAME_SENDER,
                        r.NAME_RECEIVER,
                        r.NET_AMOUNT_RECEIVER,
                        r.TOTAL_RECEIVER,
                        r.TOTAL_PAY_RECEIVER,
                        r.ID_CURRENY,
                        TransactionStatus =
                            r.ID_FLAG_RECEIVER == "A"
                                ? "VOID"
                                : r.ID_FLAG_RECEIVER == "C"
                                    ? "CANCEL"
                                    : r.ID_FLAG_RECEIVER == "E"
                                        ? "EXPIRED"
                                        : r.ID_FLAG_RECEIVER == "I"
                                            ? "DONE"
                                            : r.ID_FLAG_RECEIVER == "P"
                                                ? "PAID"
                                                : r.ID_FLAG_RECEIVER == "T" ? "TRANSFERED" : null,
                        StatusH = (
                            ((from mh in _context.MotivoHolds
                              where mh.ID_BRANCH == r.ID_BRANCH &&
                                    mh.ID_RECEIVER == r.ID_RECEIVER &&
                                    mh.TYPE_MOTIVO_HOLD == "70"
                              select new
                              {
                                  StatusH = "STAGED"
                              }).FirstOrDefault().StatusH) ?? "HOLD")
                    };

    NOTE THAT i CREATED SEPARATELY THE STATEMENT FOR <WHEN "H"> AS IT WOULD NOT GET CONVERTED AS PART OF THE INITIAL sql.

    aNY INSIGHT WOULD BE APPRECIATED.


    Thursday, August 14, 2014 3:18 PM

All replies

  • Hello,

    >>Here is the linq statement that fails:

    How does this query not work for you? if it has error message, please share it with us, if not, please describe it how it does not work for you, with your current information, we do not know what happens exactly.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, August 15, 2014 3:44 AM
    Moderator