datediff with case statement in select query linq

Question

select case statement in linq query.

Here is the query on sql:

  select case when DATEDIFF(day,convert(varchar,Min([Order].CreatedOnUtc),101),convert(varchar,Max([Order].CreatedOnUtc),101)) = 0 then
 Sum([Order].OrderSubtotal)
 else 
 case when (DATEDIFF(day,convert(varchar,Min([Order].CreatedOnUtc),101),convert(varchar,Max([Order].CreatedOnUtc),101))/30) = 0 then Sum([Order].OrderSubtotal) else
 Sum([Order].OrderSubtotal)/
  (DATEDIFF(day,convert(varchar,Min([Order].CreatedOnUtc),101),convert(varchar,Max([Order].CreatedOnUtc),101))/30)
 end 
 end as 'Account Value' from [order] where And Account.ID = @Act_ID

I am trying the code here:

 var query = _orderRepository.Table;
        query = query.Where(o => o.AccountId == accountId);

In query i am getting my value.

After query statement what should i write?? how do i write for case statement using linq???

I hope below links will help. [linq-case-statement - Stackoverflow](http://stackoverflow.com/questions/936028/linq-case-statement) [select-case-in-linq - Stackoverflow](http://stackoverflow.com/questions/4244023/select-case-in-linq) — Venu, Mar 04 '17 at 11:16
i have already look this statement in stackoverflow..but in my case DATEDIFF with two case statement..so can you guide me?? — Manoj, Mar 04 '17 at 11:21
var query = _orderRepository.Table; query = query.Where(o => o.AccountId == accountId); var MinDate = (from d in query select d.CreatedOnUtc).Min(); var MaxDate = (from d in query select d.CreatedOnUtc).Max(); var rateSum = (query.Sum(d => d.OrderSubtotal)); query = query.Where(c => (DbFunctions.DiffDays(MinDate, MaxDate) == 0) ? "rateSum" : (DbFunctions.DiffDays(MinDate, MaxDate) / 30 == 0) ? "rateSum" ? "(rateSum / (DbFunctions.DiffDays(MinDate, MaxDate) / 30)"); — Manoj, Mar 04 '17 at 12:55

score 0 · Accepted Answer · answered Mar 07 '17 at 13:15

@Manoj, may be the below code helps you. This sample C# project may solve the problem you have.

using System;
using System.Collections.Generic;
using System.Linq;

namespace DateDiffIssue
{
    class Program
    {
        static void Main(string[] args)
        {

            // Preparing data
            var data = new Order[] {
                new Order { AccountID = 1, CreatedOnUtc = DateTime.Parse("1.01.2017 10:00"), OrderSubtotal = 100 },
                new Order { AccountID = 1, CreatedOnUtc = DateTime.Parse("1.01.2017 12:00"), OrderSubtotal = 150 },
                new Order { AccountID = 1, CreatedOnUtc = DateTime.Parse("1.01.2017 14:00"), OrderSubtotal = 150 }
            };

            // Selection
            var selected = (from item in data
                            let accountData = data.Where(w => w.AccountID == 1)
                            let minDate = accountData.Min(m => m.CreatedOnUtc).Date
                            let maxDate = accountData.Where(w => w.AccountID == 1).Max(m => m.CreatedOnUtc).Date
                            let isSameDate = minDate == maxDate
                            let basedOn30Days = (maxDate - minDate).TotalDays / 30
                            let isInside30Days = (int)basedOn30Days == 0
                            let accountDataSum = accountData.Sum(s => s.OrderSubtotal)
                            select new
                            {
                                AccountValue = isSameDate ? accountDataSum :
                                                isInside30Days ? accountDataSum :
                                                accountDataSum / basedOn30Days
                            }).Distinct();

            // Print each order
            selected.ToList().ForEach(Console.WriteLine);

            // Wait for key
            Console.WriteLine("Please press key");
            Console.ReadKey();
        }
    }

    internal class Order
    {
        public int AccountID { get; set; }
        public DateTime CreatedOnUtc { get; set; }
        public int OrderSubtotal { get; set; }
    }
}

datediff with case statement in select query linq

1 Answers1