Using MAX to find a record then get other columns in the row (c#)

[kerrywales]

Can someone help me with a Framework query into a PG db. I am using fw 3.5 with dotConnect in C#.

In its simplified format I have two tables. Transactions & Clients. When a Transaction is an order then ClientID is > 0. If it is not an order, then it is 0.

Transactions
TransactionID
TransactionDate
ClientID
TransactionAmount

Clients
ClientID
ClientName

I want a list of client names and latest +ve TransactionAmount.

This SQL will get me part of the way
select "ClientID", Max("TransactionDate") from "Transactions"
Where “ClientID” > 0 and "TransactionAmount" > 0.00 group by "ClientID "

In as much as this gives me a list of the dates of newest transactions and the Client ID.

1. I am having difficulty translating this into LINQ syntax so that it does the same task
2. How to use the LINQ to get the Transaction record (so I can select the TransactionAmount) and join it to the Clients table to get to the ClientID

Can anyone advise please.

[Shalex]

Try the following LINQ to Entities:

Code: Select all

    new Devart.Data.PostgreSql.PgSqlMonitor() { IsActive = true };
    using (Entities context = new Entities()) {
       var task = from clients in context.Clients
                    join transactions in context.Transactions
                    on clients.ClientID equals transactions.ClientID into orderGroup
                    select new 
                    {   ClientName = clients.ClientName,
                        LastTransactionDate = orderGroup.Max(date => date.TransactionDate),
                        TransactionCount = orderGroup.Count()
                    };
        var a = task.ToList();
    }

If this doesn't help, please post your question at the corresponding forum. Your question exceeds the goals of our support because it is not provider-specific.