Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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?


SQL
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;


C#
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:

C#
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
[no name] 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

1 solution

You're on the right track!

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

C#
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#[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900