需要此查询才能在linq中进行转换 [英] Need this query to convert in linq

问题描述

如何在linq中转换此查询：

how to convert in linq this query :

select t.Message
from [dbo].[tblA] t
inner join (
select Message, max(CreatedDate) as MaxDate
from [dbo].[tblA]
group by Message
) tm on t.Message = tm.Message and t.CreatedDate = tm.MaxDate
where Type='Enter'

我试过的：



i需要在linq中转换此sql查询



我尝试过： < br $> b $ b

i试过这个：

What I have tried:

i need to convert this sql query in linq

What I have tried:

i have tried this :

var sender = (from item in ct.tblA
              where item.Type == "Enter"
              group item by item.Message into msgGroup
              select msgGroup.OrderByDescending(t => t.CreatedDate).FirstOrDefault().Message).ToArray();

但是这个查询返回列表1时间类型=输入的所有用户的列表...我需要输入最后状态类型的用户列表。



请帮助我。

but this query return the list of all user who has at list 1 time type = enter ...i need the list of user who has the last status type enter.

Please help me .

推荐答案

您的查询与我在此提供的查询不同：如何在linq中转换此查询 [ ^ ]



如果我的早期的解决方案返回错误的结果集，试试这个：

Your query differs from query i provided here: How to convert in linq this query[^]

If my earlier solution returns wrong result set, try this:

var sender = (from item in ct.tblA
              group item by item.Message into msgGroup
              select msgGroup.Where(e=>e.Type == "Enter")
                    .OrderByDescending(e => e.CreatedDate)
                    .First()) 
      .ToList();

测试下面的例子（通过使用<啊ref =https://www.linqpad.net/class =externaltarget =_ blank> LinqPad ）：

Tested on below example (via using LinqPad):

void Main()
{
	DataTable dt = new DataTable();
	dt.Columns.AddRange(new DataColumn[]
		{
			new DataColumn("Message", typeof(string)),
			new DataColumn("Type", typeof(string)),
			new DataColumn("CreatedDate", typeof(DateTime))
		});
	AddSampleData(ref dt);
	//dt.Dump();
	
	var latestMessages = dt.AsEnumerable()
		.GroupBy(x=>x.Field<string>("Message"))
		.Select(grp=>grp.Where(x=>x.Field<string>("Type")=="Enter")
			.OrderByDescending(x=>x.Field<DateTime>("CreatedDate"))
			.FirstOrDefault())
			.ToList();
		
	latestMessages.Dump();

}

// Define other methods and classes here
public void AddSampleData(ref DataTable dt)
{
	dt.Rows.Add(new object[]{"Hello World!", "Enter", DateTime.Now.AddHours(-5).AddMinutes(-45)});
	dt.Rows.Add(new object[]{"Hello World!", "Leave", DateTime.Now.AddHours(-5).AddMinutes(-47)});
	dt.Rows.Add(new object[]{"Hello World!", "Enter", DateTime.Now.AddHours(-5).AddMinutes(-49)});
	dt.Rows.Add(new object[]{"Hello Kitty!", "Enter", DateTime.Now.AddHours(-8).AddMinutes(-15)});
	dt.Rows.Add(new object[]{"Hello Kitty!", "Leave", DateTime.Now.AddHours(-7).AddMinutes(-55)});
	dt.Rows.Add(new object[]{"Hello Kitty!", "Exit", DateTime.Now.AddHours(-7).AddMinutes(-5)});
	dt.Rows.Add(new object[]{"Oh, Caramba!", "Enter", DateTime.Now.AddHours(-6).AddMinutes(-15)});
	dt.Rows.Add(new object[]{"Oh, Caramba!", "Leave", DateTime.Now.AddHours(-6).AddMinutes(-5)});
	dt.Rows.Add(new object[]{"Oh, Caramba!", "Exit", DateTime.Now.AddHours(-5).AddMinutes(-59)});
	dt.Rows.Add(new object[]{"Good evening!", "Enter", DateTime.Now.AddHours(-2)});
	dt.Rows.Add(new object[]{"Good evening!", "Leave", DateTime.Now.AddHours(-1).AddMinutes(-48)});
	dt.Rows.Add(new object[]{"Good evening!", "Enter", DateTime.Now.AddHours(-1).AddMinutes(-45)});
}

这篇关于需要此查询才能在linq中进行转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！