Click here to Skip to main content
14,236,939 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi! I'm a newbie to LINQ C#. Is it possible to translate my SQL query to LINQ?
Can anyone help me, please?


select Max(a.CreatedDate), Max(b.ModifiedDate), Count(*) from TableA AS a join TableB AS b on a.Id = b.Id where a.UserId = 1;


a.) TableA 
- UserID (PK, int, not null, (but NOT UNIQUE))
- Id (PK,FK, unique)
- CreatedDate (not null)

b.) TableB 
- Id (PK, unique)
- ModifiedDate (null)

There is foreign key on TableA.Id -> TableB.Id
And the last condition is that TableA.UserId = 1



I need COUNT of rows in result of that query, MAX CreatedDate and MAX ModifiedDate.

What I have tried:

------------------------------------------------------------------------------------
C#
I've tried:

var result = (from b in TabeleB
              join a in TableA on a.Id equals b.Id
              where (a.UserId == 1)
              select new { a.CreatedDate, b.ModifiedDate })
              .ToList();

var maxModifiedDate = result.Max(x=> x.ModifiedDate);
var maxCreatedDate = result.Max(x=> x.CreatedDate);
var count = result.Count();


But I don't need whole list, just three values...
I need LINQ query getting MAX of value1, MAX of value2, COUNT of resulted rows

Could you help me?
Posted
Updated 15-Aug-16 11:41am
v5
Comments
Sreekanth Mothukuru 11-Aug-16 11:31am
   
Have you tried LINQPad tool?
Member 12681421 12-Aug-16 3:38am
   
unfortunately I don't have possibility to download it
Vignesh Mani 11-Aug-16 11:47am
   
Hi could you send your question little bit details?
Member 12681421 12-Aug-16 3:05am
   
I've updated question with more details
Beginner Luck 11-Aug-16 22:08pm
   
yes
Member 12681421 12-Aug-16 3:06am
   
Could you get me some tip to do that? I've added more details in my question
Beginner Luck 12-Aug-16 3:23am
   
http://www.sqltolinq.com/ and http://www.linqpad.net/ both have tutorial how to do it
0x01AA 12-Aug-16 4:42am
   
Again me :)
UserID (PK, int, not null, (but NOT UNIQUE))
In case you really applied a Prismary Key Constraint for UserID in DB, then UserId will be unique. In case did not that, you should not describe UserId as PK otherwhise this becomes very confusing.
Member 12681421 12-Aug-16 4:48am
   
I really applied a Primary Key Constraint for UserID in DB but it is not unique. that's it.

Never mind. My problem is to write LINQ query,which is getting MAX of someValue, MAX of otherValue, and Count(*) WITHOUT getting whole list (as I wrote in question)
0x01AA 12-Aug-16 4:54am
   
Really as PK in TableA?? or maybe only in TableUser?

Yep, I try to solve this LINQ, but I'm completely newbie on this, and doing this as a great exercise for me :-)
Member 12681421 12-Aug-16 5:03am
   
Really on TableA :)
0x01AA 12-Aug-16 8:20am
   
Yes I see now how you exactly define PK in TableA :)

So for now I have this:

from a in
(from a in db.TableA
where
a.UserId == 1
select new {
a.CreatedDate,
ModifiedDate = (DateTime?)a.TableB.ModifiedDate,
Dummy = "x"
})
group a by new { a.Dummy } into g
select new {
Created = (DateTime?)g.Max(p => p.CreatedDate),
Modified = (DateTime?)g.Max(p => p.ModifiedDate),
CountA = g.Count()
}


No, no. I didn't that by myself, I downloded Linqer to do it. Now I'm trying to understand it :lol:
I hope it helps
Member 12681421 12-Aug-16 8:27am
   
thanks a lot for this!

I'll also try to understand it and maybe simplify it :)
0x01AA 12-Aug-16 10:16am
   
I hope, this is finally correct :)

from a in
(from a in TableA
join b in TableB on a.Id equals b.Id
where
a.UserId == 1
select new {
CreatedDate= a.CreatedDate,
ModifiedDate = (DateTime?)b.ModifiedDate,
Dummy = "x"
})
group a by new { a.Dummy } into g
select new {
Created = (DateTime?)g.Max(p => p.CreatedDate),
Modified = (DateTime?)g.Max(p => p.ModifiedDate),
CountA = g.Count()
}

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 3

You're on the right track!

On the first look, you have to group data...
See: How to: Perform Grouped Joins (C# Programming Guide)[^]

var query = from a in TableA.Where(u=>u.UserId==1)
            join b in TableB on a.Id equals b.Id into grp
            select new
                {
                    MaxOfCreated = grp.Max(z=>z.CreatedDate),
                    MaxOfModified = grp.Max(x=>x.ModifiedDate),
                    Count = grp.Count()
                };


Note #1: I do not see your data, so... my query may not meet your requirements...
Note #2: I'd suggest to download LinqPad[^], which is very useful in process of query translation from sql to Linq. It's free tool ;)

For further details, please see:
101 LINQ Samples in C#[^]
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100