Category Archives: LINQ

Linq to CSV

Linq to CSV

Something Old

Something old

This post is about something old, something I should have posted about a long time ago.  A few years ago I had to use a CSV file as a data source for some process.  I had forgotten all about it until recently when I was looking through some old projects, they definitely brought back some memories.

I do remember thinking there has to be a better way to access a CSV file more like a database and have it populate an object.  I started to get my frame of mind all set to come up with a solution when I thought how dumb it is to re-invent the wheel.  So I did a search first, and found the LinqToCsv package.

Read the rest of this entry

LINQ VS the NULL Monster

The Problem

I was working on a task, something that seemed just way too simple.  I needed to filter a List<T> on one of the values in the objects in the List.  I put my LINQ in place and started the tests.  This change needed to happen in different areas, but it was simple reuse of code and not a big deal and so was really only two places.  The tests returned the error about an object not set to an instance of an object.  I really don’t like that error.

Read the rest of this entry

Using LINQ to Sort by Day of Week And Time

So a couple weeks ago I saw a question on the ASP.Net or MSDN forums.  Someone wanted to know how to use LINQ to sort by the day of the week and then by the time for a given datetime.  I did not have a chance to do anything with it, but for some reason it stuck in my head.  So, I came up with the answer, but of course I do not remember where the post was located.  The answer is very simple.

Read the rest of this entry

Using LINQ And Converting DateTime To A String

The problem, I need the DateTime, but I really only need the date and I need to use it for something else.  Something that would like it to be a string.  How can I do this using LINQ?  Well it really isn’t all that hard.  If you try to use any of the .ToString or .To…. DateString you will get an error because it cannot do that in SQL.  Well don’t give up, you just need an extra step.

Basically you are going to get your data out of SQL before you try to convert that DateTime to a string.

First you do your normal LINQ query where you just get that DateTime, here is an example.

var vDates = (from d in oForm.tblSchedule
                      select new { scheduleDate = EntityFunctions.TruncateTime(d.dtSchedule) })

Now here comes the fix.  Use something like the ToList() so that it iterates on it and is no longer in SQL.  Now you can use your .To…String methods.

var vDates = (from d in oForm.tblScheduleIn
                      select new { scheduleDate = EntityFunctions.TruncateTime(d.dtSchedule) })
                      .Select(o => new {scheduleDate = o.scheduleDate.Value.ToShortDateString()});

Now I cannot claim credit for figuring this out on my own. I found an article about it on Stack Overflow.

Mask a String Using Linq Aggregate Method

I had a need to mask a string from a database.  I knew there had to be a way to do this using LINQ, and of course I knew someone had to have already done it.

So not wanting to be one to re-invent the wheel I did some searching and found this great post by Shawn Sweeney.

Mask a String Using Linq Aggregate Method.

Custom Validation with GridView and EF

This is to document how I do custom validation with a GridView in a website that uses Entity Framework.

Some assumptions:

  • I am using .NET4 with Entity Framework 4
  • I am using an EntityDataSource object
  • My GridView command buttons are templates and have the ID set as a command argument

In this example I will address doing an update from a GridView where we want to make sure a field called someName is unique. The table in question has an identity field that is unique and is called ID. Our model is MyModel and our entity is named MyEntities.

The first thing we need to do is create an OnRowCommand event for our GridView. This event will store the ID of the row we want to update. We will store this in the ViewState. My OnRowCommand event consists of a switch statement which I will put below.

        switch (e.CommandName)
            case "Edit":
                ViewState["editID"] = e.CommandArgument.ToString();
            case "Update":
                ViewState["editID"] = e.CommandArgument.ToString();
            case "Cancel":
                ViewState["editID"] = null;

The next important step is the OnServerValidate event for our custom validation control. This is where the real work happens. My OnServerValidate event puts the value into a string, and the ID into an int. It then calls another private method to set the e.isValid property to the results of the private method. The private method takes the value and ID as parameters. The check is really just counting the number of occurrences of the value in the table that does not equal the ID. Of course in your situation you may not have a string as a value, but you get the idea.

    protected void CV_UpdateUniqueName(object sender, ServerValidateEventArgs e)
        string sName = e.Value;
        int nID = Convert.ToInt32(ViewState["editID"].ToString());
        e.IsValid = IsNameUnique(sName, nID);

    private bool IsNameUnique(string sName, int nID)
        bool isUnique = false;
            using (MyEntities oEntities = new MyEntities())
                int nMatches = (from a in oEntities.tblMyTable where a.someName == sName && != nID select a).Count();
                isUnique = (nMatches <= 0);
        catch (Exception e)
            //Error handling here
        return isUnique;

There you have it, pretty simple.

An Update 6/2/2011
Thinking about it now, perhaps using control state would be better than view state, at least if you tend to turn off view state.

Dynamic Where With LINQ

It never seems to fail. At some point some one is going to want you to write something that will use one or more fields on a form to filter.  There are a number of ways to do this, but this little bit is focused on LINQ.  Why LINQ?  Because that is what I am doing at the moment, learning and really liking LINQ.

With LINQ it appears even easier than some of the traditional methods.  First you query as normal, part of that should include the fields you want to use in the where clause.  Once you have that you just filter that same query.

You simply check for the filter to have a selected or entered valid value.  Then you use the query.Where.  Inside that where you would do your filtering.  Here is an example

myQuery = myQuery.Where(b => b.statusID == nSelectedStatus);