如何在Subsonic for Subqueires中编写查询

How can I do the following query in subsonic 2.2

如何在亚音速2.2中执行以下查询

select Table1.Id, Table1.Name, Table1.Age from Table1
where Table1.Id =
(
select Max(T.Id) from Table1 T
Where T.Age = 20
)

Can one can provide me with example.

可以为我提供一些例子。

Thanks. nRk

3 个解决方案

#1

Subsonic 2.2 can do sub-queries:

Subsonic 2.2可以进行子查询:

As Adam suggested, editted and improved example using In, this works for me:

正如Adam建议的那样,使用In编辑和改进示例,这对我有用:

SubSonic.Select s = new SubSonic.Select(SSDAL.Customer.CustomerIDColumn, SSDAL.Customer.NameColumn);
SubSonic.Aggregate a = new SubSonic.Aggregate(SSDAL.Customer.CustomerIDColumn, SubSonic.AggregateFunction.Max);
SSDAL.CustomerCollection COL = new SSDAL.CustomerCollection();
SubSonic.Select sq = new SubSonic.Select("LastCustomerId");
sq.Aggregates.Add(a);
sq.From(Tables.Customer);
a.Alias = "LastCustomerId";
s.From(Tables.Customer);
s.Where(SSDAL.Customer.CustomerIDColumn).In(sq);
COL = s.ExecuteAsCollection<SSDAL.CustomerCollection>();
;

This code produces the following SQL:

此代码生成以下SQL:

SELECT [dbo].[Customer].[CustomerID], [dbo].[Customer].[Name]
 FROM [dbo].[Customer]
 WHERE [dbo].[Customer].[CustomerID] IN (SELECT MAX([dbo].[Customer].[CustomerID]) AS 'LastCustomerId'
 FROM [dbo].[Customer])

#2

Adam may be onto something but it looks a little ugly. Here is an example that is a bit more readable that returns an IDataReader

亚当可能会做些什么,但看起来有点难看。这是一个更具可读性的示例,它返回一个IDataReader

new SubSonic.Select(Table1.IdColumn,Table1.NameColumn,Table1.AgeColumn)
.From(Table1.Schema)
.Where(Table1.IdColumn).In(new SubSonic.Select(Aggregate.Max(Table1.IdColumn)).From(Table1.Schema))
.ExecuteReader();

#3

As far as I know there is no support for subqueries in SubSonic. You'll need to put the query in a sproc and use the generated SPs.SprocName() method.

据我所知,SubSonic中不支持子查询。您需要将查询放在sproc中并使用生成的SPs.SprocName()方法。

EDIT: I was wrong, see other answer below.

编辑:我错了,请看下面的其他答案。

#1