Mike Admire’s Blog

I’ve been using LINQ for my data access piece in .NET for the last few months and I thought I would write a quick post on how I’ve found to do grouping. I’ve seen some examples where a ‘group by new’ is done with a list several fields to group by. I’ve also seen similar examples in SQL, but this is often times not what is wanted. In SQL it generally requires a join to a second query that has the grouping field and any aggregates that are needed. In LINQ, a way that I’ve found to work for me is something like this:

var q = from invoice in context.Invoices
    where invoice.ManifestDate >= startDate && invoice.ManifestDate <= endDate
    group invoice by invoice.PoNumber into g
    select new {
        PoNumber = g.Key,
        g.First().InvoiceNumber,
        g.First().ManifestDate,
        ShippingCharge = g.Sum(p => p.ShippingCharge),
        ProductCharge = g.Sum(p => p.Quantity * p.NetPrice),
        g.First().CreateDate
    }

This allows me to group only by PoNumber in this example and calculate aggregates on ShippingCharge and ProductCharge from the group. In this example this is only going to use the first value found in the group for the value returned, which is an acceptable solution for me in this scenario.

In SQL this might look something like:

SELECT DISTINCT
    inv.PoNumber,
    inv.InvoiceNumber,
    inv.ManifestDate,
    g.ShippingCharge,
    g.ProductCharge,
    inv.CreateDate
FROM Invoice inv
JOIN (
    SELECT
        PoNumber,
        SUM(Quantity * NetPrice) as ProductCharge,
        SUM(ShippingCharge) as ShippingCharge
    FROM Invoice
    GROUP BY PoNumber) g
ON inv.PoNumber = g.PoNumber

This error came up today using FtpWebRequest in .Net. I have used the same delete method without any problems on other FTP servers, but for some reason this server was causing me some issues. I did some searching and didn’t find much except for one comment on an MSDN forum about KeepAlive. This turned out to be the issue and by setting FtpWebRequest.KeepAlive to false fixed the problem and I haven’t seen it since.

private void Delete(string remoteFile) {
    string deleteRequest = ftpServer + remoteFile;
    FtpWebRequest request = (FtpWebRequest)WebRequest.Create(deleteRequest);
    request.Method = WebRequestMethods.Ftp.DeleteFile;
    request.Credentials = new NetworkCredential(ftpLoginName, ftpLoginPassword);
    request.Proxy = null;
    request.UseBinary = false;
    request.UsePassive = true;
    request.KeepAlive = false;
    FtpWebResponse response = (FtpWebResponse)request.GetResponse();
    Stream responseStream = response.GetResponseStream();
    StreamReader sr = new StreamReader(responseStream);
    sr.ReadToEnd();
    string StatusCode = response.StatusDescription;
    sr.Close();
    response.Close();
}