Have Geek Brain

How to write a request in Subsonic for Subqueires

How can I fulfill the following query in subsonic version 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 you provide me with an example.

Thanks. NRK

+1


a source to share


3 answers


Subsonic 2.2 can do subqueries:

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

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 creates the following 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])
+3


a source


Adam might be on to something, but looks a little ugly. Here is an example, which is more readable, that the IDataReader returns



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


a source


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

EDIT: I was wrong, see another answer below.

-1


a source






More articles:


All Articles