LINQ and Dynamic Query – Comparing DateTime value

By: lakswin

In one of our development, we end up with the situation that needs to check the DateTime value in Dynamic LINQ query. Trying different ways out there and googling a bit, finally got some useful references that helped us to resolve the same

Hope, you would love reading!



TestEntities te = new TestEntities();

string dateString = “12/01/2010″;


Used the following overload for “.Where” :

# 1:

IQueryable<T>.Where(Expression<Func<State, bool>> predicate)

var dateTest = te.SiteLogs

.Where(String.Format(“it.TimeStamp >= DATETIME ‘{0}’”, Convert.ToDateTime(dateString).ToString(“yyyy-MM-dd HH:mm”)))



# 2:

IQueryable.Where(string predicate, params object[] values)

var dateTest1 = te.SiteLogs

.Where(“it.TimeStamp >= @0″,  Convert.ToDateTime(dateString))




In # 1,

  • We need to say the “Time” format also, that is compulsory. Otherwise it would throw an error as well.  And again, the syntax with “DATETIME” is wired 🙂
  • The resultant date format should be “yyyy-MM-dd “


In # 2,

Its clean and simple. I like this syntax. Here you can add any number of Params like @0, @1 & so on




Copy from:

Image Resizing and Cropping in C#

By: JasonJano’s Blog

So the other day I put up a c# wrapper for ffmpeg. The wrapper I built is a part of my own asset storage system which I guess I will probably be putting up in chunks over the next few weeks. Today I have decided to put up my image resizing tools for your use.

Resize Now or Resize Later?

There are a couple theories to image resizing, should the image be resized at the point it gets uploaded, or at the point it gets sent out to the client?

Each method has advantages and disadvantages. Resizing at upload is good because it speeds up time to delivery as the processor has already done the resizing, additionally, the image is only resized once, vs. if the image is resized every time it is downloaded, it could be resized countless times. The problem with resizing at upload, however, is that it is now resized — you can’t un-resize it. Resizing at download then, is more processor intensive as it is resized every time someone requests a different size (one could probably get around some of this with caching, but the no matter how you slice it, it is still more processor intensive). The nice thing is that based on querystring or, however you pass the resizer data, you get a customized image size.

I have my own theory on this. If one has a ton of storage space (which most of us do have available to us now adays) it makes sense to resize in some basic sizes at upload while keeping the original as well for future resizing, if needed. Most good graphic designers break all their pages up into grids of magic 3rds anyways and would, generally, have ideas about their perfect image sizes for thumbnails, swatches, large previews, etc..

In another post I will detail how I get all of these different sizes and what not stored for use, but — for now — I will just give you the tools with which to do resizing and cropping in one spot.

//Overload for crop that default starts top left of the image.
public static System.Drawing.Image CropImage(System.Drawing.Image Image, int Height, int Width)
    return CropImage(Image, Height, Width, 0,0);
//The crop image sub
public static System.Drawing.Image CropImage(System.Drawing.Image Image, int Height, int Width, int StartAtX, int StartAtY)
    Image outimage;
    MemoryStream mm = null;
        //check the image height against our desired image height
        if (Image.Height < Height) {
            Height = Image.Height;
        if (Image.Width < Width) {
            Width = Image.Width;
        //create a bitmap window for cropping
        Bitmap bmPhoto = new Bitmap(Width, Height, PixelFormat.Format24bppRgb);
        bmPhoto.SetResolution(72, 72);
        //create a new graphics object from our image and set properties
        Graphics grPhoto = Graphics.FromImage(bmPhoto);
        grPhoto.SmoothingMode = SmoothingMode.AntiAlias;
        grPhoto.InterpolationMode = InterpolationMode.HighQualityBicubic;
        grPhoto.PixelOffsetMode = PixelOffsetMode.HighQuality;
        //now do the crop
        grPhoto.DrawImage(Image, new Rectangle(0, 0, Width, Height), StartAtX, StartAtY, Width, Height, GraphicsUnit.Pixel);
        // Save out to memory and get an image from it to send back out the method.
        mm = new MemoryStream();
        bmPhoto.Save(mm, System.Drawing.Imaging.ImageFormat.Jpeg);
        outimage = Image.FromStream(mm);
        return outimage;
    catch (Exception ex)
        throw new Exception("Error cropping image, the error was: " + ex.Message);
//Hard resize attempts to resize as close as it can to the desired size and then crops the excess
public static System.Drawing.Image HardResizeImage(int Width, int Height, System.Drawing.Image Image)
    int width = Image.Width;
    int height = Image.Height;
    Image resized = null;
    if (Width > Height)
        resized = ResizeImage(Width, Width, Image);
        resized = ResizeImage(Height, Height, Image);
    Image output = CropImage(resized, Height, Width);
    //return the original resized image
    return output;
//Image resizing
public static System.Drawing.Image ResizeImage(int maxWidth, int maxHeight, System.Drawing.Image Image)
    int width = Image.Width;
    int height = Image.Height;
    if (width > maxWidth || height > maxHeight)
        //The flips are in here to prevent any embedded image thumbnails -- usually from cameras
        //from displaying as the thumbnail image later, in other words, we want a clean
        //resize, not a grainy one.
        float ratio = 0;
        if (width > height)
            ratio = (float)width / (float)height;
            width = maxWidth;
            height = Convert.ToInt32(Math.Round((float)width / ratio));
            ratio = (float)height / (float)width;
            height = maxHeight;
            width = Convert.ToInt32(Math.Round((float)height / ratio));
        //return the resized image
        return Image.GetThumbnailImage(width, height, null, IntPtr.Zero);
    //return the original resized image
    return Image;

Copy from:

Visual Studio 2012 / 2013 Update 1 2 3 4 Offline Installer


Visual Studio 2012 Update 2 was released about a week ago.  This update includes lots of fixes and some features – you can see the list here.  The only problem with the update is that Microsoft does not offer an offline installer.  If you are installing this on your own PC or for one person, you may not have a need for one.  But…  if your entire team needs to install this (or you just want to have it for later for a PC rebuild, you can download all 1.8 GB and have an offline installer for you or your team to share.  Here’s how:

  1. Get the update from Microsoft here. (updated with Update 4 link)
  2. Save the file to a folder.
  3. open the folder
  4. Pro tip – Shift + right-click the background of the folder and choose ‘Open command window here’
  5. open_command_window
  6. in the command window type VS2013.4.exe /Layout
  7. (or VS2013.1.exe /Layout or VS2013.2.exe /Layout depending on your update)
  8. command_window
  9. It will then ask you where you would like to save and extract the files.
  10. vs2012_update_2_download_location
  11. This step will take some time depending on your download speed.  This is the step that adds time to the install when you choose the web installer.
  12. downloading_extracting_update_2
  13. You will now see a folder with the update installer along with all the source files.  You can now store this safe on a network share or your hard drive and update your installation of Visual Studio without downloading 2GB of files!
  14. vs2012_extracted
  15. This should work for any version of Visual Studio going forward.

Copy from:

Points, inches and Emus: Measuring units in Office Open XML

By: Lars Corneliussen

After I got confused with the Office Open XML (OOXML) measuring units for a couple of hours, I thought I’d share what I found on it.

I order to avoid floating point calculations and still maintain high precision the format uses some odd measurement units. Don’t think you can get away with inches, centimeters or pixels!

UPDATE: I created a conversion tool for all those units, available here.

Twentieths of a point (dxa)

The main unit in OOXML is a twentieth of a point. This is used for specifying page dimensions, margins, tabs, etc.

The international default letter size is ISO 216 A4 (210x297mm ~ 8.3×11.7in) and expressed as this:

// pageSize: with and height in 20th of a point
<w:pgSz w:w="11906" w:h="16838"/>


20th of a Point Points






Width 11906 595.3 8,27… 21.00086…
Height 16838 841.9 11.69… 29.70036…

As you see it doesn’t really come out even, but even enough. As you can see here, word processes files at 72dpi.


Half-points are used to specify font sizes. A font-size of 12pt equals 24 half points:

// run properties
  // size value in half-points
  <w:sz w:val="24"/>

Fiftieths of a Percent

This is used for relative measurements in some places. It can for example be used for specifying tables total with, cell with and margins.

      <!-- table width in 50th of a percent -->
      <w:tblW w:w="2500" w:type="pct"/>
                    <w:t>Hello, World!</w:t>

This prints a table that takes 50% of the available with. If you want to specify the with in twentieth of points instead, you have to use specify w:type=”dxa”.


0.5 * 5000 = 2500pct



EMUs (English Metric Unit)

EMUs are used for coordinates in vector-based drawings and embedded pictures. The EMU is a virtual unit to bridge both centimeters and inches. One inch equates to 914400 EMUs and a centimeter is 360000. Actually I found out that the number 914400 is calculated by (the least common multiple of 100 and 254) times 72. As I understand it, this ensures that you can convert forth and back between integer 100th inches, millimeters and pixels with out any floating points. Is that correct, anybody?

Since OOXML is too verbose, the full markup would just confuse even more. Lets say we have a picture that we want to fit into a table cell.

The cell with is 4,25cm which equals to 2410 dxa.

<w:tcW w:w="2410" w:type="dxa"/>

The original picture is 295x413px at 72dpi and is embedded using DrawingML. The target picture size in the document has to be specified twice. Once for the drawing canvas (extent) and a second time for the embedded picture itself.

When you try to draw this in word itself you’ll have a hard time to match the actual cell size. This version is set by manually calculating the target picture size.

    <wp:anchor ...>
        <!-- drawing canvas size -->
        <wp:extent cx="1530350" cy="2142490"/>
                    <!-- shape properties -->
                    <pic:spPr bwMode="auto">
                        <!-- 2D transform -->
                            <!-- picture size -->
                            <a:ext cx="1530350" cy="2142490"/>


20th of a Point Points






Cell Width 2410 120.5 1,67361… 1530350

Even though this is the most logical way to calculate it, it involves floating points. You can simply avoid those by calculating:

2410 * 914400 / 72 / 20 = 1530350

which can be simplified to:

2410 * 635 = 1530350

Since we want to maintain the aspect ratio of the original picture at 295:413 we have to calculate the height for the cell in dxas and for the pictures in EMUs.

The width-to-height-ratio for this picture is 1.4. So the height for the cell is 2410*1.4 = 3374 dxa and 3374*635 = 2142490 emu.

Thanks for reading! I hope this helps.


Copy from:

Export data from a DataSet into a real Excel 2007 file

Ever wanted to add an “Export to Excel” function to your ASP.Net, WinForms or WPF application ?

This free C# and VB.Net library lets you export a DataTable or DataSet of data into a “real” Excel 2007 .xlsx file, using one line of code.

CreateExcelFile.CreateExcelDocument(myDataSet, myExcelPathFilename);

…or if you’re using ASP.Net…

CreateExcelFile.CreateExcelDocument(myDataSet, myExcelFilename, Response);

You can download the full source code using the links below, so you can extend it to add Excel formatting, etc.

It uses the OpenXML libraries, rather than Microsoft’s Visual Studio Tools for Office (VSTO) libraries, so you don’t need to have Excel installed on your server.

The CreateExcelFile library

All of the code you’ll need is contained in one class, CreateExcelFile, which I’ve saved in the file CreateExcelFile.cs (or CreateExcelFile.vb for the VB.Net version).

To use this class, you simply call its CreateExcelDocument function, passing it a DataSet variable (which contains the values you want writing into Excel cells), and the path+filename of the Excel file you wish to create.

Each DataTable within your DataSet will be saved into it’s own Excel worksheet.

// Step 1: Create a DataSet, and put some sample data in it
DataSet ds = CreateSampleData();

// Step 2: Create the Excel .xlsx file
string excelFilename = “C:\\Sample.xlsx”;
CreateExcelFile.CreateExcelDocument(ds, excelFilename);
catch (Exception ex)
MessageBox.Show(“Couldn’t create Excel file.\r\nException: ” + ex.Message);

Demo program

The attached Visual Studio 2010 C# and VB.Net WinForms demo shows how easy it is to use the CreateExcelFile library.

It consists of a simple dialog with one button on it (shown below).

When you click on the button, it’ll create a DataSet and fill it with some sample data, and prompt you for a location to save our example Excel file to. It will then call the CreateExcelDocument function to create an Excel 2007 .xlsx file containing our DataSet’s data, then open the file in Excel for you to admire.

Screenshot from the C# demo Screenshot from the C# demo

Notice how our demo application created a DataSet containing three DataTables in it, called Drivers, Vehicles & Vehicle Owners. The CreateExcelFile library then created an Excel Worksheet for each of these DataTable names, and wrote each DataTable’s data into it.

Using the “Export to Excel” library in your own application

The CreateExcelFile library has two dependences:

  • DocumentFormat.OpenXml.dll
    From the Microsoft Open XML SDK library
  • WindowsBase.dll
    From the Microsoft .Net Framework library

I have included a copy of these two files in the source code’s lib folder, but ideally, you should download the most recent version from the Microsoft website.

To use the library in your own code, simply add these two files, and the CreateExcelFile.cs file to your Visual Studio project, then add the two .dll files to the References section of your project.

Also, you need to make sure that these two files have the “Copy Local” field set to true. Without this, when you deploy your app, it won’t copy the .dlls to your bindirectory, and IIS will complain that it can’t find the OpenXML libraries.

Then just call the static CreateExcelDocument function, as shown above.

CreateExcelFile.CreateExcelDocument(ds, excelFilename);

I have deliberately left this library’s source code available for you to view and alter, as desired.

Exporting from a List<> or DataTable

With a little help from a discussion on CodeGuru, I added two extra functions, so you can now call the CreateExcelDocument function in three ways:

public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)
public static bool CreateExcelDocument(DataSet ds, string xlsxFilePath) you should find it really easy to export your data to Excel from any of these formats.

Using the library in an ASP.Net application

November 2013: With help from my fellow developers, I have made the C# library even easier to use in an ASP.Net application. The ExportToExcel class now lets you create an Excel file without writing to a temporary file first. You just need to pass the “Response” as a parameter.

// In this example, I have a defined a List of my Employee objects.
class Employee;
List<Employee> listOfEmployees = new List<Employee>();

// The following ASP.Net code gets run when I click on my “Export to Excel” button.
protected void btnExportToExcel_Click(object sender, EventArgs e)
// It doesn’t get much easier than this…
CreateExcelFile.CreateExcelDocument(listOfEmployees, “Employees.xlsx”, Response);

By default, this functionality is disabled in the C# file, so that non-ASP.Net developers don’t get build errors when attempting to use the library. To enable the functionality, you need to manually make two small changes:

First, uncomment the first line of code in the CreateExcelFile.cs file, so it looks like this:


Then add a new Reference, and select the System.Web library.

Add Reference..

And that’s it. You can now use the three new web-friendly functions for exporting to Excel.

public static bool CreateExcelDocument<T>(List<T> list, string filename, System.Web.HttpResponse Response)
public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponse Response)
public static bool CreateExcelDocument(DataSet ds, string filename, System.Web.HttpResponse Response)

Cool, hey ?

You’re welcome to use and adapt this code as you like, but – please – if you like it, leave me a comment below.

Setting the Excel column widths

By default, the CreateExcelFile class just creates a valid Excel 2007 with no formatting, colors, borders, etc.

If you wanted to change this code to set the widths of the columns in your Excel file, below are the changes you’d need to make.

First, you need to add a few lines to the CreateParts function (shown in red):

private static void CreateParts(DataSet ds, SpreadsheetDocument spreadsheet)
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dt in ds.Tables)
// For each worksheet you want to create
string workSheetID = “rId” + worksheetNumber.ToString();
string worksheetName = dt.TableName;

WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

// If you want to define the Column Widths for a Worksheet, you need to do this *before* appending the SheetData
// The following example sets the column width, for all columns where we have data, to 20 pixels.

int columnWidthSize = 20;
Columns columns = new Columns();

for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
Column column = CustomColumnWidth(colInx, columnWidthSize);

// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);

Then, add the following function just below the WriteDataTableToExcelWorksheet function:

private static Column CustomColumnWidth(int columnIndex, double columnWidth)
// This creates a Column variable for a zero-based column-index (eg 0 = Excel Column A), with a particular column width.
Column column = new Column();
column.Min = (UInt32)columnIndex + 1;
column.Max = (UInt32)columnIndex + 1;
column.Width = columnWidth;
column.CustomWidth = true;
return column;

If you wanted to calculate the column widths, based on the data in each column, it’s not easy.
Have a read of this article:


C# VB.Net Filename Description The full Excel demo, showing how to use the CreateExcelFile library code, and the Open XML libary files.
CreateExcelFile.cs / .vb Just the C#/VB.Net source code file for the CreateExcelFile class. The executable (.exe) file of the Demo, if you just want to see what the application looks like.
Note: This zip file contains a copy of the .exe file created by the Visual Studio project which you can download using the link above. This .zip file is safe, but your anti-virus software might complain about it or block it, as it contains an executable file.

Copy from:

Extending your ASP.NET Web API responses with useful metadata


If you ever worked with any API, which, in this day of age, you must have, you surely noticed that in most situations the API response isn’t just the result (requested data), but also a set of helpful metadata, like “total Results”, “timestamp”, “status” and so on.

In Web API, by default, you just serialize your models (or DTO) and such information are not present. Let’s build something which will solve this problem and help you decorate your response with hepful information. This would make it very easy for the client to implement paging, auto-loading scenarios, caching (if you return last modified information) and a lot more.


What are we going to build

First let’s go through a plan of what we are going to build.

Assume you have some sample repository. In the recent samples, I have always been using a dummy repository from this post, so let’s use it again.

Now, by default, the response will look like this (for a single item):


If we request for IQueryable, we obivously get an Array.

Now, what we’d like to have is something like this:


So much nicer isn’t it?

Designing the service

So how are we going to accomplish this? Pretty simple:
1. We will implement a DelegatingHandler which will capture all HttpResponseMessages, extract the response object and wrap into our custom generic Metadata. Then it will be flushed to the client.
2. Additionally, we’ll have a CustomQueryableAttribute, which we will use on IQueryableActions, to keep the information about the size (count) of the IQueryable. This way we will be able to provide information about what is the total size of the collection and thus support OData filtering. This way the client can request i.e. $top=3 results, but still have information about the total size of the IQueryable.


As mentioned, we will use a repository from here – it’s really simple and perfect for testing. Let’s have a look at the Metadata instead.



It is a generic class, which will provide information such as:
– total results (since the data may be filtered)
– returned results (since the data may be filtered)
– results object (which is equal to the default WebAPI response)
– timestamp of the response
– status – to indicate a successful or unsuccessful response

I will leave the constructor empty for now, it will be more clear to what’s happening there, once we go through the handler and filter.

The Data annotations are there for compatibility with DataContractSerializer. Content negotation is supported, but in principle, this functionality is better suited for JSON.NET, but more on that later.


MetadataHandler will inherit from a DelegatingHandler, which means it will have access to theHttpResponseMessage before it is flushed to the client and before applying MediaFormatting.



As mentioned, we need to modify the response, and to do that we need to overrideTask<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken) method and add a ContinueWith to it.

In there, we extract the object from the response and pass it to a private method for altering.

Now, here is a note. If you serialize to JSON, using JSON.NET you can do just that, it can handle that just fine. On the other hand, if you are willing to use DataContractSerializer, it will not be able to serialize object types properly. You would need known types, so you’d need either reflection or typecasting:


Anyway, going back to our example, the ProcessObject method:


In this case, we instantiate a new Metadata and set it as the content of the response. As you see, I arbitrairly set the formatter to JSON.NET. The commented out code preservers content negotiation, but if you use that you’d need to type cast the objects as mentioned before.

We also used a simple helper method to check if the response is even worth processing:


Let’s now revisit the constructor we omitted earlier.



The constructor takes the HttpResponseMessage and builds up useful information based on it – i.e. setting the “sucess” or “error”, and also calculating the number of returned results.

Note, if single item is requested (via regular Get(int id) type of action, not OData) it will show 1 total result, because that’s how many matches there were. Total results is greater than returned results only if you filter data with OData.

Adding support for OData and IQueryable

All of this wouldn’t be very useful if we didn’t add our key functionality, which is “total results”. To do that, we need to introduce a new attribute filter, CustomQueryableAttribute, inheriting fromQueryableAttribute.

We’d then decorate all IQueryable actions with it.


We override the method IQueryable ApplyResultLimit(HttpActionExecutedContext actionExecutedContext, IQueryable query), which gives us access to IQueryable prior to applying the filtering. What it means, is that we can easily save aside the total number of results of theIQueryable and then use it later in our Metadata object.

We use a bit of a hack here, since I save that in a custom header field “originalSize”. In the MessageHandler later on, we will read that value from the headers and remove it so that they don’t get sent to the client – so it only saves the purpose of transporting a variable from the ActionFilter to the MessageHandler. If you want to pass data between two ActionFilters you could useControllerContext.RouteData.Values, but for our scenario I couldn’t find a better way.

Now we need to update the MessageHandler, to make it aware of the “originalSize”:


So we read the header value and get rid of the redundant header key.

Registering handler, decorating methods

The final thing to do is to register the handler, so in App_Start we add:


For testing, I get rid of the XmlFormatter as well.

We also add our custom queryable filter to all Action’s returning IQueryable:


Running the application

Everything is ready, so let’s roll.

First let’s get all Urls:

Let’s test OData:

Now let’s get a single Url:

Now, just to show that this doesn’t really on any strong types, let’s add a new repository with Blogobjects.

Summary & source code

Hopefully someone will find the functionality described here useful. It can be obviously extended further, and optimized (perhaps someone can figure out a better way to handleDataContractSerializer).


Copy from: