是否可以将我的SQL查询转换为LINQ？ [英] Is it possible to translate my SQL query to LINQ?

问题描述

嗨！我是LINQ C＃的新手。是否可以将我的SQL查询翻译成LINQ？

有人可以帮帮我吗？

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

我在查询结果中需要COUNT行，MAX CreatedDate和MAX ModifiedDate。



我的尝试：



------------ -------------------------------------------------- ----------------------

C＃

我试过了：

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

但是我不需要整个列表，只需要三个值...

我需要LINQ查询得到MAX值为1，MAX为value2，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?

推荐答案

你走在正确的轨道上！



首先看，你必须分组数据...

参见：如何：执行分组连接（C＃编程指南） [ ^ ]

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

注意＃1：我不知道看到你的数据，所以...我的查询可能不符合你的要求...

注2：我建议下载 LinqPad [ ^ ]，这在过程中非常有用从sql到Linq的查询翻译。这是免费工具;）



详情请见：

C＃中的101个LINQ样本 [ ^ ]

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#[^]

这篇关于是否可以将我的SQL查询转换为LINQ？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！