Need this query to convert in linq

Question

0.00/5 (No votes)

See more:

how to convert in linq this query :

SQL

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'

What I have tried:

i need to convert this sql query in linq

What I have tried:

i have tried this :

C#

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();

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 .

Posted 7-May-18 3:59am

stelazh

Updated 7-May-18 9:34am

Wendelius

v3

Add a Solution

Comments

Maciej Los 7-May-18 14:13pm

Please, respect the rules and do not repost!
How to convert in linq this query[^]?
If you have any question, you have to post comment to the author of answer.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-05-07T09:35:00

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:

C#

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();

Tested on below example (via using LinqPad):

C#

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)});
}