CODEWITHSUNDEEP
linq
KDeveloper
KDeveloper

Reputation: 3

How can you handle this sub-query with LINQ to SQL?

I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL:

SELECT * 
FROM UnitPrice
WHERE EffectiveDateTime = (SELECT MAX(EffectiveDateTime) 
                           FROM UnitPrice AS InnerUnitPrice 
                           WHERE InnerUnitPrice.EffectiveDateTime < GETDATE())

Upvotes: 0

Views: 62

Answers (3)

&#193;ngel Ib&#225;&#241;ez
&#193;ngel Ib&#225;&#241;ez

Reputation: 359

Linq lamda query:

            var q = db.UnitPrice
                .Where(x1 => x1.EffectiveDateTime == db.UnitPrice
                                                    .Where(x2 => x2.EffectiveDateTime < DateTime.Now)
                                                    .Max(x3 => x3.EffectiveDateTime)
                )
                .ToList();

Upvotes: 0

Anu Viswan
Anu Viswan

Reputation: 18155

You could do the following.

Emulating your scenario with DataTable

var unitPrice = new DataTable();
unitPrice.Columns.Add("EffectiveDateTime",typeof(DateTime));
unitPrice.Columns.Add("SomeOther",typeof(string));
unitPrice.Rows.Add(new DateTime(2018,12,1), "Sample1");
unitPrice.Rows.Add(new DateTime(2018,12,2), "Sample2");
unitPrice.Rows.Add(new DateTime(2018,12,3), "Sample3");
unitPrice.Rows.Add(new DateTime(2018,12,4), "Sample41");
unitPrice.Rows.Add(new DateTime(2018,12,4), "Sample4");
unitPrice.Rows.Add(new DateTime(2019,12,4), "Sample5");

You can query the required result as,

var result = unitPrice.AsEnumerable()
             .Where(x=>x.Field<DateTime>("EffectiveDateTime") < DateTime.Today)
             .GroupBy(x=>x.Field<DateTime>("EffectiveDateTime"))
             .OrderByDescending(x=>x.Key)
             .First().ToList();

Output

04-12-2018 00:00:00 Sample41 
04-12-2018 00:00:00 Sample4

Upvotes: 1

Gufus
Gufus

Reputation: 513

I give you an example on this short list of objects

List < Entry > list = new List<Entry>();

            list.Add(new Entry("a","_a",new DateTime(2019,1,30),1));
            list.Add(new Entry("b", "_b", new DateTime(2018, 12, 31), 2));
            list.Add(new Entry("c", "_c", new DateTime(2018, 12, 31), 3));
            list.Add(new Entry("d", "_d", new DateTime(2018, 12, 30), 4));
            list.Add(new Entry("e", "_e", new DateTime(2018, 11, 30), 5));

the properties of the class Entry are, in order Reference (string), Donation (string), Date (datetime), Amount (double).

First step, we want to select the most recent date which is before today (what you're doing in your subquery); starting from our list we could do something like this:

var max_date = from l in list
                           where l.Date < DateTime.Today
                           group l.Date by 1 into g
                           select new { Val=g.Max() };

            foreach (var m in max_date)
            {
                 Console.WriteLine(m.Val);
            }

Running this you'll get 31/12/2018 as desired. But we need another step to select all the informations on the selected date. So, step 2, we put our starting list in inner join with the one we've just built:

var list2 = from l in list
            join m in max_date on l.Date equals m.Val
            select l;

            foreach (var l in list2)
            {
                Console.WriteLine($"{l.Reference}, {l.Donation}, {l.Date}, {l.Amount}");
            }

the result will be

b, _b, 31/12/2018 00:00:00, 2
c, _c, 31/12/2018 00:00:00, 3

as desired. Hope it helps!

Upvotes: 0

Related Questions