by Wojciech Sura

Powershell philosophy

One way to think about Powershell is that it is a shell version of IEnumerable Linq extensions.

For instance, let’s imagine, that we want to know the size of the biggest .wav file in the folder. If we was in C#, the code would probably look like the following:

[csharp]int size = Files.Where(f => f.Name.Contains(".wav")).OrderBy(file => file.Size).Last().Select(f => f.Size);[/csharp]

PowerShell version differs only by the syntax, philosophy is the same:

[shell]PS D:\Temporary> Get-ChildItem | Where-Object {$_.Name -like "*.wav"} | Sort-Object -Property Length | Select-Object -last 1 | Select-Object Length[/shell]

They’re very similar, aren’t they?

by Stian

Missing relations in EntityFramework model when generating from database

Talk to the handWe recently had a problem where the generated model in Entity Framework 4 would not create all the relationships we had in our database.

We were moving from a Linq2Sql model to Entity Framework and tried to automatically generate the model from the database, but we kept missing a few of the relationships. After hours of looking at the missing relationships in the database, comparing them to see why these would go missing while all others were ok, we got a hint at StackOverflow to check the indexes.

Having a Unique Non-Clustered Index on the primary key caused a problem for the EF model and changing these to a Unique Clustered Index fixed the problem.

by Njål

C#: Removing duplicate entries using LINQ

Here’s a pretty neat line of code that removes duplicate entries (ID is specified by you) from a IEnumerable:

class MyTestObject{
    public string MyKey;
    public int SomeValue;
    ...
}


//myList contains MyTestObjects - some of these objects
//have the same MyKey value, and I only want objects with unique MyKey values

var uniqueCollection = myList.GroupBy(x => x.MyKey).Select(y => y.First());

by Andreas

LINQ – how to build queries dynamically

In a previous post I described a way to build dynamic WHERE clauses to achieve the WHERE [value] IN [array] functionality using LINQ. This post will show how you can combine LINQ with lambda to achieve a different type of dynamic / runtime query building. (The example is using Entity Framework 4.2).

I came across a need for this when dealing with Guid columns in SQL Server. In code, Guid.Empty isn’t NULL, it’s “00000000-0000-0000-0000-000000000000”. This doesn’t go too well with the unique index of the table, which of course ignores NULL values but considers “00000000-0000-0000-0000-000000000000” a real value – which it is! So the challenge was to alter the LINQ query depending on one of the input parameters of the method. If the Guid parameter is equal to Guid.Empty, I want to query the database for NULL values in that column. If the Guid has a valid value, query the database using it the way it is.

I made this work by adding a conditional IF, but for readability I have split it up in an old fashion IF / ELSE. Define the LINQ query, and based on the input parameter “parentFolderId” define the WHERE statement using lambda :

var existingFolder = from f in db.Folders
             select f;

if (Guid.Empty.Equals(parentFolderId))
{
    // parent folder is empty, which is NULL in DB (Guid.Empty = '00000000-0000-0000-0000-000000000000')
    existingFolder = existingFolder.Where(p => p.FolderName == folderName && p.ParentFolderID == null);
}
else
{
    existingFolder = existingFolder.Where(p => p.FolderName == folderName && p.ParentFolderID == parentFolderId);
}

if (existingFolder.FirstOrDefault() != null)
    return false; // folder already exists

// create new folder

Remember that a LINQ query isn’t executed until the result set is accessed. In this case the database isn’t queried until the line where “existingFolder.FirstOrDefault()” is executed, and the desired result is achieved with minimal code.

Tags:
by Andreas

LINQ – How to build a dynamic WHERE clause (C#)

LINQ is brilliant, and if you know how (and not to mention where, when and on what) to use it it will really improve the readability of your code and save you a hell of a lot of time. One thing that is missing though, is a direct implementation of the “in” statement, known from SQL. If you want to run a query which relies on runtime values, you would want to build the where clause dynamically. The equivalent of this SQL statement:

SELECT * FROM USERS WHERE USERID IN (12, 14, 32, 86)

in LINQ should have been like this (the userIds array is simulating dynamic runtime values):

var userIds = new List<long>() { 12, 14, 32, 86 };
var result = from u in dbContext.Users
                    where u.UserId in userIds
                    select u;

But it’s not. So while we wait for the LINQ team to implement it, here’s a solution to get around this challenge. Use the Contains method of the dynamic array this way:

var userIds = new List<long>() { 12, 14, 32, 86 };
var result = from u in dbContext.Users
                    where userIds.Contains(u.UserId)
                    select u;

This will not only compile, but it will also give you the same result as the SQL statement above.

Tags: ,