Recently in C# Category

I choose to love it.

Here is a post that I posted on Stack Over Flow, asking for some assistance in generating the following query seen below: 

Complicated query with aggregate data for a report from multiple tables for an ordering system

from pmt in products_mainTable
join opt in orders_productsTable on pmt.guid equals opt.products_mainTableGUID into tempProducts
from orderedProducts in tempProducts.DefaultIfEmpty()
join omt in orders_mainTable on orderedProducts.orders_mainTableGUID equals omt.guid into tempOrders
from ordersMain in tempOrders.DefaultIfEmpty()
group pmt by new { pmt.sku, orderedProducts.color, orderedProducts.size } into g
orderby g.FirstOrDefault().sku
select new {
    g.FirstOrDefault().guid,
    g.Key.sku,
    g.Key.size,
    QTY = g.FirstOrDefault().orders_productsTable.Sum(c => c.qty),
    SUM = g.FirstOrDefault().orders_productsTable.Sum(c => c.itemprice * c.qty),
    AVG = g.FirstOrDefault().orders_productsTable.Average(c => c.itemprice * c.qty),
    Some = g.FirstOrDefault().orders_productsTable.Average(p => p.qty).GetValueOrDefault(0),
};

I’ve got to say that working with LINQ-TO-SQL has been an absolute pleasure.  I even fired up LINQPad and have really been enjoying the use of that program.  The $19 for the IntelliSense was definitely worth it.  What a great tool.

I can now take the query above into my code, and render it into reports, grids, etc. and whatever else I need it for using my DevExpress framework.  Exciting stuff, to be sure.

I’ll write some more on this topic when I have some additional time, but since I had a difficult time Googling for the answer on my Stackoverflow question, I thought I’d write about it here.

The above represents how to do a full LEFT OUTER JOIN in LINQ and C# while performing GROUPING and AGGREGATE functions.  Hopefully this will be a good starting place for someone else looking to do the same thing.

The important bits here are DefaultIfEmpty() which helps to perform the LEFT OUTER JOIN and FirstOrDetaulf() which allows you access to the underlying type.  While I knew about DefaultIfEmpty() I did not know about FirstOrDefault().. using FoD was the real key to making this query work the way that I wanted it to.

Now I am able to quickly and easily query my sales data.  This query for instance queries the products table, finds products that have sold and generates a quick report that I can use as a DataSource for other components.

Viva la LINQ!

Matthew MacSuga

I had a requirement for a project that I am working on.  The project consists of a Windows 2003 Web Server serving up custom C# Web Services, and a public-facing LAMP [Linux, Apache, MySQL, PHP] web server.  I have certain data that I am storing in a MySQL database that must be encrypted.  Given that this is a webserver symmetric encryption does not make sense because if someone gained access to the PHP files, they would have the password to decrypt the data!!  That would be bad.

So I chose to do asymmetric encryption, thus only the Public Key would reside on the server.  The PHP scripts would encrypt the data, store it in a database, and when needed would be transmitted to the web-services server and decrypted using C#.  This proved to be a rather daunting task however. 

PHP requires either an X509 certificate, or a PEM certificate.  On the surface, .NET does neither!  The RSACryptoProviderService was able to create keys, but they were in some XML format which was incompatible with what I needed to do with PHP.

It turns out that I needed to use X509 certificates to accomplish what I needed.  Yikes, I had absolutely no idea what X509 was and how I was going to use it.  Little did I know that the internet was going to conspire against me and make finding all of the information that I needed virtually impossible.  After spending countless hours going over the MSDN, web pages, and other resources.. I finally managed to figure out what exactly needed to be done.

Following this blogpost on using Makecert I was able to create a certificate authority to sign my certificate, and create a certificate.  I installed in it in the Certificate Store, exported BOTH the Private and Public key file.  The public key I exported in BASE64 (second option) and the private key in PKCS#12 (PFX) format.  The private key is readable by .NET for the decryption.

I sent the public key to my PHP webserver.  Using the following code I was able to encrypt a string:

Now on the .NET side.. here is the code I used to decrypt the data using a private certificate file:

/// <summary>
///Decrypt data
/// </summary>
/// <param name="Base64EncryptedData"></param>
/// <param name="PathToPrivateKey"></param>
/// <returns></returns>
public static string DecryptEncryptedData(stringBase64EncryptedData, stringPathToPrivateKeyFile) {
    X509Certificate2myCertificate;
    try{
        myCertificate = newX509Certificate2(PathToPrivateKeyFile);
    } catch{
        throw newCryptographicException("Unable to open key file.");
    }

    RSACryptoServiceProvider rsaObj;
    if(myCertificate.HasPrivateKey) {
         rsaObj = (RSACryptoServiceProvider)myCertificate.PrivateKey;
    } else
        throw newCryptographicException("Private key not contained within certificate.");

    if(rsaObj == null)
        returnString.Empty;

    byte[] decryptedBytes;
    try{
        decryptedBytes = rsaObj.Decrypt(Convert.FromBase64String(Base64EncryptedData), false);
    } catch {
        throw newCryptographicException("Unable to decrypt data.");
    }

    //    Check to make sure we decrpyted the string
   if(decryptedBytes.Length == 0)
        returnString.Empty;
    else
        returnSystem.Text.Encoding.UTF8.GetString(decryptedBytes);
}

There you have it! That's it in a nutshell. By using the X509Certificate2 class, I was able to read in the key [this reads in several key formats, by the way], then create the RSACryptoServiceProvider object and cast the .PrivateKey field into it, and perform the decryption.

- Matthew