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

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

Postby kerrywales » Thu 16 May 2013 08:58

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.
kerrywales
 
Posts: 52
Joined: Tue 05 Jan 2010 12:26

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

Postby Shalex » Tue 21 May 2013 11:14

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.
Shalex
Devart Team
 
Posts: 7660
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL