LINQ is awesome solution for creating efficient queries with dynamic WHERE conditions

by jasonvonruden 25. January 2011 14:16

I have been using this method for a long time and I just want to share an example of applying dynamic “WHERE” conditions to make more efficient queries.

For example you could create a query using the “LIKE” verb to handle a query with multiple where clauses when you have the potential of having only 1 parameter and you are expecting the other parameter to wildcard.  You will quickly find out that if field that you are wildcarding with just “%” is null you will not match on it when you where expecting a match. This example of applying dynamic “WHERE” conditions totally solves that solution and it is much cleaner code as an added bonus.

Example of just using ‘LIKE’ conditions:

As you can see the query will be less efficient because you are filtering an a column that you may not need to based on the input parameters provided. Must include the function to convert nulls into empty strings, yuck.

SELECT r
FROM XrfReservation r
WHERE ISNULL(r.ReservationProject,'') like :dd and
      ISNULL(r.ReservationName,'') like :di and
ORDER BY r.ReservationProject, r.ReservationName

Example of of applying dynamic “WHERE” conditions:

As I hope that you will see this code looks nicer and is more efficient!

//*******************************************************************
// Get XrfReservations By ReservationProject and ReservationName
//*******************************************************************
public IList<XrfReservation> GetXrfReservationsByProjectName(string _reservationproject, string _reservationname, int _resultpage, int _resultpagesize)
{
    //Paging Logic
    int firstResult = 0;
    if (_resultpage > 1)
    {
        firstResult = (_resultpage - 1) * _resultpagesize;
    }
 
    //Linq - base query
    var xrfReservations = from r in _Session.Query<XrfReservation>()
                          select r;
 
    //Linq: Apply where clauses
    if (!string.IsNullOrEmpty(_reservationproject))
    {
        xrfReservations = xrfReservations.Where(_w => _w.ReservationProject == reservationProject);
    }
 
    if (!string.IsNullOrEmpty(_reservationname))
    {
        xrfReservations = xrfReservations.Where(_w => _w.ReservationName == _reservationname);
    }
 
    //Linq Apply order by.
    var xrfReservationsOrderBy = from o in xrfReservations orderby o.ReservationProject, o.ReservationName select o;
 
    //Linq: Apply paging.
    IList<XrfReservation> xrfReservationsList = xrfReservationsOrderBy.Skip(firstResult).Take(_resultpagesize);
 
    //Execute Query and return list of data
    return xrfReservationsList.ToList();
}

Tags: , ,

c# | LINQ | NHibernate

Use SharpZipLIb to create zip file directly from database without saving any temporary files on the hard disk

by jasonvonruden 28. July 2010 00:13

The Goal

I needed to package up inspection photos into a zip file and make them available for download directly from a website.   The inspection photos are stored not stored in the file system, instead they are stored in a MySql database via NHibernate. 

My Approach

Every example that I could find involved reading the database, writing out the files to a temporary working directory on the hard disk,  zip up the files using SharpZipLib, and then delete the temporary files.  

This seemed like a lot of extra work!

So I decided to create my zip file completely in-memory and skip writing out the files to working directory on disk.

Steps:

  1. Determine if inspection has any photo attachments.
  2. Create Zip File Memory Streams using SharpZipLib a .NET compression library that supports Zip files using both stored and deflate compression methods, PKZIP 2.0 style and AES encryption, tar with GNU long filename extensions, gzip, zlib and raw deflate, as well as BZip2.
  3. Read every photo on the database and create zip entries in memory streams.
  4. Finalize the zip file.
  5. Return the zip file to the user via website http response stream.

Generate Inspection Photo Zip File

//*****************************************************************************
//Generate Inspection Photo Zip File
//*****************************************************************************
public byte[] GenerateInspectionPhotosZipFile(Inspection _inspection)
{
    //Open Zip File.
    MemoryStream zipOutMemoryStream = new MemoryStream();
    ZipOutputStream zipOutStream = new ZipOutputStream(zipOutMemoryStream);
 
    //Add Photos to Zip File.
    foreach (var inspectionAttachment in _inspection.fkInspectionAttachments)
    {
        if (inspectionAttachment.Attachment != null && inspectionAttachment.Attachment.Length > 0)
        {
            MemoryStream photoMemoryStream = new MemoryStream(inspectionAttachment.Attachment);
            ZipEntry entry = new ZipEntry(inspectionAttachment.AttachmentName);
            zipOutStream.PutNextEntry(entry);
            byte[] photoBytesBuffer = photoMemoryStream.ToArray();
            zipOutStream.Write(photoBytesBuffer, 0, photoBytesBuffer.Length);
            photoMemoryStream.Dispose();
            zipOutStream.CloseEntry();
        }
    }
    zipOutStream.Finish();
    zipOutStream.Close();
    zipOutMemoryStream.Close();
    byte[] responseBytes = zipOutMemoryStream.ToArray();
    zipOutMemoryStream.Dispose();
    zipOutStream.Dispose();
 
    //Return Null on Empty Zip File
    const int ZIP_FILE_EMPTY = 22;
    if (responseBytes.Length <= ZIP_FILE_EMPTY)
        return null;
 
    return responseBytes;
}

Return Photo to User Via Http Output Response Stream

//*****************************************************************************
 //Build Inspection Photo Zip File
 //*****************************************************************************
 private void GenerateInspectionPhotosZipFile(NHibernateDataProvider _provider, Inspection _inspection)
 {
     //Generate Inspection Photos.
     byte[] responseBytes = _provider.GenerateInspectionPhotosZipFile(_inspection);
 
     //Return Zipfile in Response Output Stream
     if (responseBytes != null)
     {
         //Response Return Zip File
         Response.ContentType = "application/zip";
         Response.AppendHeader("Content-Disposition", string.Format("attachment; filename=InspectionPhotos_{0}_{1}_{2}.zip", _inspection.Id.Inspectionid, _inspection.Bankid, _inspection.Processorid));
         Response.OutputStream.Write(responseBytes, 0, responseBytes.Length);
     }
 }

Tags:

c# | NHibernate