How to: Server Side Filter database (Entity Framework + DevExpress Filter control)

Imagine you have a table with millions of rows. It’s not a good idea showing all these data in a grid or similar, and it doesn’t matter you’re developing a web or desktop application… It’s not a good idea at all.

In these cases we should use conditional filters applied in the data source (usually a database) to get part of the information and return only the rows that match with the specified criteria. Using this technique the amount of network traffic could be reduced to a hundreds of rows or less, depending on the criteria.

There are different ways of accomplish that, but today I’d like to focus on apply this technique using a control from the DevExpress suite: The Filter Editor control.

DXFilterControl

 

As you can see the control looks nice, and it’s quite simple to use: You just need to link this control to another (usually a Grid or Tree), and automatically will retrieve the columns and values information from the source control. Then you’ll be able to filter the linked control and get a filter string which you can reuse later in other controls of the suite.

However the previous filter has the disadvantage that is a client filter, and is applied after we retrieve all the rows from the data source, not before. Our goal have to be apply the filter before getting the information.

Today I’ve been playing a little bit with this control, using a SQL Server database that contains a products table with 3.7 million rows and Entity Framework 6.0 as an ORM.

Note: For this test I’d like to use a SQL Tabled-Valued Function to access the data instead of using the default DbSet, because it’s supposed that I’ve to do some hard calculations inside the function. But you can also use the EF DbSet directly, as usual.

EFModel

Now our goal here is:

  1. Create at runtime a filter over the Products table using the filter control.
  2. Parse that filter string and adapt it to the EF syntax.
  3. Append the filter to the mapped function (thx to IQueryable!) before execution.
  4. Finally, execute the filtered query against the data source

First we need to add an EF designer (we won’t use Code First because we’re going to map a Tabled valued function with is not supported). And add the table Products and the GetAllProducts function.

In our form, we just need to add a couple of Devexpress controls: A GridControl and a FilterControl, linked using the FilterControl SourceControl property (this allows the filter control to retrieve the names and data types of the grid columns).

DynamicFiltersEF

Then we’ll add three buttons. The first one will retrieve the first 25.000 rows from the database (previously I’ve tried to get all rows from table, but the app crashed with an OutOfMemory exception ;))

The second button (this is the interesting one) will execute the current filter from the FilterControl.

Let’s code:

The first button is really simple. It’s just a query over the GetAllProducts function adding a where clause which will be resolved later (because EF is based on IQueryable<T>) when calling ToListAsync method.

private async void simpleButton1_Click(object sender, EventArgs e)
{
    using (var context = new TestBigDbEntities())
    {
        var filteredproducts = context.GetAllProducts().Where(p => p.ProductId <= 25000);
        await ExecuteQueryAsync(filteredproducts);
    }
}

private async Task ExecuteQueryAsync(IQueryable filteredProducts)
{
    clock.Restart();
    productBindingSource.DataSource = await filteredProducts.ToListAsync();
    clock.Stop();
    Text = clock.ElapsedMilliseconds.ToString("n2");
}

For the second button we need something more. Something that allows us to translate the filter into a valid where clause, and append it to the function.  The good news are that this functionality is provided natively by Devexpress:

private async void simpleButton2_Click(object sender, EventArgs e)
{
    var converter = new DevExpress.Data.Linq.CriteriaToEFExpressionConverter();
    using (var context = new TestBigDbEntities())
    {
        var filteredproducts =
                DevExpress.Data.Linq.Helpers.CriteriaToQueryableExtender.AppendWhere(
                context.GetAllProducts(),
                converter,
                filterControl1.FilterCriteria);
        await ExecuteQueryAsync(filteredproducts);
    }
}

All we’ve to do is that:

Clean and simple. The first one creates a converter for translating the filter into EF syntax, while the second one appends the filter to the EF function using the previous converter.
And finally, here’s the SQL in the profiler. Exactly what I’ve expected.

SQLProfiler

So well… if you’re using Devexpress in your current project there’re no excuses for not including dynamic filters that user can save and reuse at runtime, uh? :P

Happy coding!

Bonus

The third button code shows how to get the TSQL syntax from the Filter control, that you can use it to query the database directly ;)

private void simpleButton3_Click(object sender, EventArgs e)
{
    var tsql = DevExpress.Data.Filtering.CriteriaToWhereClauseHelper.
        GetMsSqlWhere(filterControl1.FilterCriteria);
    MessageBox.Show(tsql);
}

BonusTSQLFilter

Extract extra large Icon from a file, including network paths!

Edit (04/16/2014): Added a new function called GetBitmapFromFolderPath, which returns the associated icon from the path to a Folder (or drive!).

image

Sometimes we are required to show a list of files with their associated icons. This task sounds quite easy, and in fact it is… Except if you have to deal with files in network paths or you want to get different icons sizes, apart the typical 32×32.

If you want to achieve this using managed code (the easy way), there is a static method called ExtractAssociatedIcon under the class Icon to achieve that, but sadly this method doesn’t work with UNC paths neither return other sizes that 32×32 pixels.

In my current project I needed to show four different icons sizes (including the extra-large icon, also called “jumbo”), so I’ve decided to use some functions and structures from the Win32 API. Of course, if anyone knows a better way to do it, please contact with me ASAP :)

First, let’s see the final code:


private static IntPtr GetIconHandleFromFilePath(
    string filepath, IconSizeEnum iconsize)
{
    var shinfo = new Shell.SHFILEINFO();
    const uint SHGFI_SYSICONINDEX = 0x4000;
    const int FILE_ATTRIBUTE_NORMAL = 0x80;
    const int ILD_TRANSPARENT = 1;
    uint flags = SHGFI_SYSICONINDEX;
    var retval = SHGetFileInfo(filepath, FILE_ATTRIBUTE_NORMAL,
        ref shinfo, Marshal.SizeOf(shinfo), flags);
    if (retval == 0) throw (new System.IO.FileNotFoundException());
    var iconIndex = shinfo.iIcon;
    var iImageListGuid = newGuid("46EB5926-582E-4017-9FDF-E8998DAA0950");
    Shell.IImageList iml;
    var hres = SHGetImageList((int)iconsize, ref iImageListGuid, out iml);
    var hIcon = IntPtr.Zero;
    hres = iml.GetIcon(iconIndex, ILD_TRANSPARENT, ref hIcon);
    return hIcon;
}

In this code we’re using several API calls. Here’s the tricky part:

First, we need to make call to the SHGetFileInfo function that receives a reference to a structure of type SHFILEINFO, which contains the index of the icon image within the system image list. We will use this index later.

Then we’ve to make is a second call to the SHGetImageList function that receives an output parameter with an IImageList structure, which is modified within the function.

This struct retrieve a COM interface and we need to keep in mind a couple of things:

a) We must use the GUID of this interface in the declaration:

[ComImportAttribute()]
[GuidAttribute(“46EB5926-582E-4017-9FDF-E8998DAA0950”)]
[InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIUnknown)]

b) And in the improbable case you are going to deploy your project over XP, remember that SHGetImageList is not exported correctly in XP. For this reason you must hardcode the function’s entry point. Apparently (and hopefully) ordinal 727 isn’t going to change…

Once we have that COM interface, we only need to call its GetIcon method, passing a parameter with the desired size, and obtaining a handle to the icon by reference.

After having the handle its really simple create an Icon from the handle, and then convert to a Bitmap, BitmapSource or other:


public static System.Drawing.Bitmap GetBitmapFromFilePath(
 string filepath, IconSizeEnum iconsize)
{
    IntPtr hIcon = GetIconHandleFromFilePath(filepath, iconsize);
    var myIcon = System.Drawing.Icon.FromHandle(hIcon);
    var bitmap = myIcon.ToBitmap();
    myIcon.Dispose();
    DestroyIcon(hIcon);
    SendMessage(hIcon, WM_CLOSE, IntPtr.Zero, IntPtr.Zero);
    return bitmap;
}

Tip: It’s very important don’t forget to destroy the resources (Icon) when working with the Win32 API!

This method calls the previous one, obtains the icon’s handle and then creates the icon using the handle. Then creates a bitmap from the icon, destroys the icon and returns the bitmap.

I’ve also created an enumeration with the different flags values:

private const int SHGFI_SMALLICON = 0x1;
private const int SHGFI_LARGEICON = 0x0;
private const int SHIL_JUMBO = 0x4;
private const int SHIL_EXTRALARGE = 0x2;
private const int WM_CLOSE = 0x0010;

public enum IconSizeEnum
{
 SmallIcon16 = SHGFI_SMALLICON,
 MediumIcon32 = SHGFI_LARGEICON,
 LargeIcon48 = SHIL_EXTRALARGE,
 ExtraLargeIcon = SHIL_JUMBO
}

Finally, retrieving the icon from a file (inclusive if it’s in a network location) and specify the desired size it’s as easy as:

var size = ShellEx.IconSizeEnum.ExtraLargeIcon;
var ofd = new OpenFileDialog();
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
    labelFilePath.Text = ofd.FileName;
    pictureBox1.Image = ShellEx.GetBitmapFromFilePath(ofd.FileName, size);

}

image

Hope this helps! ;)

Download the source code.

Global Windows Azure Bootcamp

On April 27, something very cool is happening. A bunch of Windows Azure MVP’s and community activists are organizing a Global Windows Azure Bootcamp. This is a completely free, one-day training event for Windows Azure, all organized by the community, and presented in person all over the World.

clip_image001

I’m not sure if this is the largest community event ever – it is very cool to see how many places this event is happening.  Below is the location map as it stands today – and new locations are being added daily. Right now there are almost 100 locations and several thousand attendees already registered to take part.  Browse the location listings to find a location near you.

clip_image003

If you are interested in learning about Windows Azure or want more info, checkout the Global Windows Azure Bootcamp website to learn more about the bootcamps.  Then find a location near you, sign-up and attend the event for free, and get involved with the Windows Azure community near you!

Hope this helps,

MVP Site interview (full version)

MVPPartySeattle2011_thumb[1]

Yesterday there was published my brief interview in the MVP site.

http://mvp.microsoft.com/en-US/Pages/default.aspx (english)

However, it is not the full version, that I publish here. Hope you enjoy it ;)

Lluís Franco Interview

Born in Barcelona (Spain) with more than 20 years of experience in the software development sector, Lluis Franco holds a number of Microsoft official certifications such as MCP and MCTS and has been recognized as MVP the past 10 years due to his active participation and moderation of the Spanish MSDN forums since 1997.

Lluis currently works as project development manager at FIMARGER a financial services company based in Andorra, where he founded the AndorraDotNet user group in 2007, being the first and almost unique user group based in this country.  Since 2010 he is also the president of the Andorra IT Professionals Virtual Association.

He maintains several blogs and is a regular host and speaker in local events in the region, such as the “Geek-A-Paloozaa” conference that took place in April 2012. He also contributes articles to the technological section of a local newspaper.

He resides in Andorra for more than 20 years, he is married and father of two girls.

1. What does it mean being an MVP?

It is something much more important to me now than when I was first awarded, as I wasn’t aware at that time of all the wonderful things that would happen to me and the great people I would meet over the time. Not only at a technical level, what the MVP community of course has, but also for the human quality of the majority of MVPs. I met many of my current best friends thanks to the MVP Program.

2. How was your start in the community?

It happened just by chance. As it was the case of many professionals in the IT industry I needed help to solve a problem and posted a question in the today disappeared Microsoft newsgroups, where I got the answer to my questions. I just loved this way of learning from other community members and one day I decided to give something back to them and I started to reply questions there. Sometime after I started running a webpage, a blog, another blog…. At some point I did a presentation at a local user group meeting and finally I started a new user group, hosting events… And I still really enjoy doing these things today.

3. What would you ask Steve Ballmer?

Let me see… The first thing that comes to my mind is to ask about some Microsoft business segments long-term strategy. But this is something that almost everybody would ask him, so I would rather ask about the good old times… So Steve, in your opinion, what has been the most important fact in Microsoft’s history? I am talking about the key factor or point in time that has made Microsoft what it is today.

4. What is the best software product in history?

I am in love with C#. For me this is the most beautiful, complete, and powerful programming language ever created. So therefore, the best software product for me is Visual Studio 2012, the best IDE in history (or at least the best I have ever used, that are many). Talking about operating systems, I have no doubt; Windows 8 is far the best one. At that point I have to say that I have not always worked with Microsoft products.

5. What would you change on Visual C#?

I would continuously add functional characteristics to it. In the latest versions we have seen the introduction of generic functions, delegates management, LINQ and moreover, lambda expressions have made of C# the programming language I have always dreamed about. It is really fun programming on C#! However some things are still missing such as Nested functions, for example. So as some of my friends mentioned to me, this would make functions to be considered 1st level citizens. There are also other things such as making the Framework and IDE a bit lighter. There are many different types of objects there and some collections could be eliminated.

6. Which is the last book you have read?

I am a compulsive technical book reader. But I also love fiction and science fiction books. I have last read ‘Ready Player One’, a novel with plenty of references of the 80’s : videogames, music, films, role games, comics and even toys. It has a young writing style and I could identify myself with the background described there. I also read technical books and of course, my favorite one: ‘The Silmarillion’.

7. Which music CD would you recommend?

Just one? In that case I would go for Radiohead’s ‘Ok Computer’. I really love this band’s the first albums. I like different kind of music from Bach to the heavy metal, including Jazz music too.

8. What makes you an excellent MVP?

I don’t consider myself an “excellent MVP”. For me being an MVP means a person with some technical skills but that is willing to share his knowledge and passion with the community in different ways, helping others that have helped them before. So the most important thing here is having the right attitude. So somebody that considers himself as being better than others because of the MVP Award probably wouldn’t deserve this recognition.

9. What is in your “Computer Bag”?

There is less and less software and more and more hardware in there. Many small devices, adaptors, that make life easier when I’m travelling. And of course, and old souvenir from the old MVP Program times, a Swiss knife with the MVP logo on it that contains a complete set of different tools, that I of course, have never used, but I hold it just in case.

10. What is the best thing that happened to you since you are an MVP?

Popularity, women, money… (I’m kidding). Maybe having being nominated as “C# MVP Of The Year 2011” as it is an Award that you can have once you are MVP and the winner is chosen by other MVPs within the same expertise. My first trip to Seattle was also great, but if I had to choose just one thing, this would be the people that you meet. There isn’t anything better than a “geeks & beers” meeting.

11. What is your motto?

It is something really simple: “Try to be a better person”, somebody able to be a model for my daughters when they will be older.

12. Who is your hero?

Sheldon Cooper, Yoda, Spock, Frodo, Cálico Electrónico and Fuckowsky from ‘Memories of an engineer’… But if I had to choose just one, this would be Tony Stark: Playboy, millionaire, genius, and with his own armor. However in real life the true heroes are those that do the most difficult thing: doing what is right under any circumstance, for eg. a child with a serious disease that encourages his parents in the bad moments…  This is really a true hero.

13. What does success mean to you?

Enjoying your daily work! In fact this would include two things: first of all having a job, what is not easy in today’s world and secondly, doing what you really enjoy for life. This is a true luck in life and what really can make you happy.

14. What would be your personal Project for this year?

Let’s see… I have already been in Seattle this year, attending to the MVP Summit, and also have organized the “Geek-a-paloozaaa” event in Andorra. So I would like to finish a series of technical posts about parallel computing that I have started some months ago, for what I don’t find the time to at this moment. In the personal side, I would like to spend more time with my loving ones.

See you :-D

Today’s review – Advanced Installer

Hi again guys! :)

Today I’d like to tell you why I decided to change my software deployment method.

ClickonceHell

I’ve spent a lot of time in the last days looking for an alternative way to deploy client applications. During this period I’ve tested different options (including the options provided by Microsoft Visual Studio) and finally I’ve decided to use Advanced Installer.

But before that I wanna tell you why I’ve arrived to this point.

My situation

As a developer I’m still building some Windows applications, because most of these applications are targeted to the business market. And well, until now I’ve been using the typical Setup projects or ClickOnce, depending on each project.

Until now.

Because ClickOnce is not the best solution for complex deployments. It’s quite good for simple or medium scenarios, but is not enough when you need to use merge modules -or equivalent-, database scripts, registry entries, launch conditions, etc.

What about Setup projects? Well, I decided not to use them anymore since I read this post. Because these modules won’t be supported in future versions of Visual Studio. If you haven’t read this post, and you’re still using it, you must read it now.

So, in the previous link you’ve noticed that WiX is recommended instead of Setup projects, but in my opinion WiX is not a valid option, because doesn’t allow neither use designers to customize the installer nor install prerequisites.

For your information: The following table compares the features of Windows Installer Setup Projects, InstallShield 2010 Limited Edition, and the Windows Installer XML Toolset.

Advanced Installer

This application provides a powerful and easy way for making your deployments via Windows Installer plattform, offering different editions: From a freeware with the most common options to an edition for architects, with lots of stunning features like integration with App-V, VMware or System Center deployments.

In my particular case I downloaded a trial version (limited to 30 days) and started to make a test with a medium-size solution, with 20 projects inside. After playing a couple of hours with the tool, I built a complete setup project, including some advanced features like: Merge modules, file associations, launch conditionals (both system and software), prerequisites, registry entries and sql scripts. And of course with complete control over the installation options and the appearance of the setup wizard.

SetupOptions

WOW! Not bad at all… And most of these options are provided by free (not all but most of them).

Coming soon

Now I’m thinking in implement some advanced features, like licensing, trials  and updates. Yes, you can implement a licensing mechanism, and not only in a simply way. You can use your own C++ DLL, or send a POST to a URL to validate the license. It sounds really good!

Another interesting feature when using serial validation allows you to make trial packages. It allows your users to evaluate your product before purchasing, and also enables users to register after they have received a valid key. After the trial period expired the user will be able to run your application only after registration.

Licensing

Finally, my favorite: Advanced Installer provides a way to check for updates. If this option is enabled, before installing the application package, a version check will be executed. If a newer version of the installation package (current MSI) is available it will be downloaded and installed instead of the current one.

My review

A stunning product, with lots of options for making your deployments quickly and easy. You can customize your setup experience in great detail and the UI helps you to create complex installation packages in minutes.

Compared with other market leaders is much easier to use and it has a shortest learning curve.

Sincerly impressed. Good job guys! :)

Advanced Installer website: http://www.advancedinstaller.com/

PS – <IRONIC MODE ON> Ok. I understand traditional Windows applications are from the past, and nowadays everybody is developing web applications or WP7/Metro applications… this kind of developments doesn’t need too complex deployments, and maybe it’s better for everybody the marketplace-based model… But there are a few people that are still developing business applications. I just wanna say… PLEASE MICROSOFT, DON’T FORGET US! :)

Amazing: Awarded MVP of the Year!

mvplogohor

Surprised. Puzzled. Amazed. These are the words that define better my current feelings.

However, while writing these lines I’m just realizing I have a blissful smile, or maybe just a stupid smile. Why? Because I just received an email from Lisa Feigenbaum (Microsoft Community Program Manager) with this message:

“Congratulations on being awarded C# MVP of the Year based on your contributions in 2011!”

WOW! That’s amazing, and sincerely, totally unexpected

For the last 9 years I have been awarded a Microsoft MVP, and believe me, that’s something very important for me. Maybe not for being recognized as an ‘expert’ (it is hard to use that word while there are so damn good people out there), but because they recognize the work of helping the community.

So now, in a private survey all the C# MVPs around the world and the product team have decided to recognize my contributions with the ‘C# MVP of the year’ award, whatever that means.

On the next March 2nd there will be an honorary dinner sponsored by S. Somasegar (“Soma”), Senior Vice President of the Developer Division at Microsoft, with other executives and community leads in the division. Unfortunately, I won’t be able to attend because I come back from Seattle the same day in the morning. Nevertheless I promise you while I am in Seattle I’ll try to have a beer with some of these guys.

From here I’d like to congratulate the rest of the winners (there are 29 in all categories) and thank all my colleagues. Hey people, you’re great!

Kind regards from Andorra,

PS – Next week I´ll start telling you my experiences @ Seattle.

Windows 8: Microsoft’s full ARM tablet demo

Microsoft introduced a number of tablet concepts during a partner preview event earlier this week.

win8tabletarmpreview

The software giant held a technical demonstration at Computex on Thursday to demonstrate Windows 8 to hardware partners. Mike Angiulo, corporate vice president of Windows Planning, Hardware and PC Ecosystem at Microsoft, demonstrated Windows 8 on a variety of tablets. Angiulo showed how Windows 8 works across x86 and ARM-based chips. Microsoft previously introduced a technology preview of its Windows ARM support by showing off an early build of Windows 8 at CES 2011. Microsoft is partnering with ARM-based manufactures NVIDIA, Qualcomm and Texas Instruments to produce new Tablet devices.

See the full article and video al Winrumors:

http://www.winrumors.com/windows-8-watch-microsofts-full-arm-tablet-demo-video/

Edit: My friend Hadi just tweet about more videos on Windows8:

Hadi Hariri

More Windows 8 Videos: http://hadih.me/jXI0ip