How to get multiple result set of procedure using LINQ to SQL

There always be case where one procedure returns more then one result set. Getting those data in DataSet is lazy way of coding, best way to do that is using DataReader. DataReader having method call NextResult which allows us to read next result set if any.

Read more from here.

Using let in LINQ to Objects – Performance killer if used wrong way

C# 3.0 LINQ has one more hidden and powerful feature; which provides you to store result of sub-expression in order to use subsequence clause. You can achieve this with the help of let keyword. The variable created using let is readonly; once initialized it can’t use to store another value only good this is it can be queried.

Let’s see this with example. We have employees details in text file delimited my ‘:’, also each employee have it’s own details separated by ‘,’.

string strEmployees = "1, John, Methew:2, Nick, Althoff:3, David, Oliver:4, Sam, Peterson";

Lets write query to grab all the employee details.

string strEmployees = "1, John, Methew:2, Nick, Althoff:3, David, Oliver:4, Sam, Peterson";

//Split with :
var query = from empData in strEmployees.Split(':')
            select empData;

//Split with ,
foreach (var q in query)
{
    var e = q.Split(',');
    Console.WriteLine("Id - {0}, First Name - {1}, Last Name - {2}",
        e[0], e[1], e[2]);
}

As you can see we have to write two different logic to split one more time employee details, now let’s use let keyword and make coding easy.

string strEmployees = "1, John, Methew:2, Nick, Althoff:3, David, Oliver:4, Sam, Peterson";

var query = from empData in strEmployees.Split(':')
            let emp = empData.Split(',')
            select new { Id = emp[0], FName = emp[1], LName = emp[2] };

foreach (var q in query)
{
    Console.WriteLine("Id - {0}, First Name - {1}, Last Name - {2}",
        q.Id, q.FName, q.LName);
}

In both query output will be same.



emp is intermediate enumerable type which we are using in next line! This is very simple example, now what compiler treats the code above? Compiler will create one sub-query that returns the anonymous type composed of the original value along with new value specified by the let.

As its creating sub-query if you write bunch of let statements, it will kill your performance. If its implemented in proper way let is very good option to go with, the scenario where you need some function which operates on your select clause more then 2-3 times, you can create let variable and use them into your select which makes your faster.

static void SummOfferNoLet()
{
    var q = from c in Products
            where SumOffers(c) < 10000 && SumOffers(c) > 1000
            select c;
    int count = q.Count();
}
static void SummOfferWithLet()
{
    var q = from c in Products
            let offerValue = SumOffers(c)
            where offerValue < 10000 && offerValue > 100
            select c;
    int count = q.Count();
}

In this case SummOfferWithLet will faster as you can see SummOfferNoLet we need to call SumOffers twice.

Conclusion: Using let is powerful but if you used wrong way then it will kill the performance.

Sequence contains no element - LINQ

Recently using Aggregate function in LINQ I come across sequence contains no element error. After checking I found the list on which I am calling Aggregate function does not have any element!!!

Problem:
ERROR : Sequence contains no element, while using Aggregate function in LINQ

Lets see with example.

I have created AssignMe function which returns the collection of string, here is the function body.

private static string[] AssignMe()
{
    return new string[] { };
}

For generating error AssignMe is returning nothing, now lets apply Aggregate function on this

string[] sList;
sList = AssignMe();
Console.Write(sList.Aggregate((first, second) => first + second));

This will throw InvalidOperationException => "Sequence contains no elements".

Lets change the AssignMe function little bit.

private static string[] AssignMe()
{
    return new string[] { "NoException" };
}

This will work perfectly, and gives you output as NoException, don't get confused with NoException, Its returning result.

Solution:

We should check the item Count before applying Aggregate function, as we seen it's working well for single element. Lets put back our original AssignMe function and change the calling method.

string[] sList;
sList = AssignMe();

if (sList.Count() > 0)
    Console.Write(sList.Aggregate((first, second) => first + second));

Working fine without an error :)

Conclusion:
While using Aggregate function or any other function like lamda expression or delegate; which accept argument then its better to check the Count of collection before applying such function.

Creating comma separated string from Array - LINQ

Its common requirement to create comma separated string from Array. Using extension methods of LINQ we can do it in single line. Lets create Array first from comma separated values, its simple as we just need to split them using Split function.

string strTest = "1,2,4,6";
string[] Nums = strTest.Split(',');

let's create comma separated string from string Array Nums,

Generally people use foreach loop to create the comma separated string,

But here is the single line code using Lambda Expression to create the same thing.

Console.Write(Nums.Aggregate<string>((first, second) => first + "," + second));
//OUTPUT:
//1,2,4,6

Here the Lambda expression which is accepting two argument and returning the string having concatenation along with delimiter.

Querying Selected Items From ListItemCollection Using LINQ

Working with ListItemCollection is general practices and getting single value for that it's like child's play. What if you need the list of items which are selected?

I am sure you probably use foreach loop something like

List<ListItem> selectedList = new List<ListItem>();
foreach(ListItem li in cbList.Items)
{
    if (li.Selected)
        selectedList.Add(li);
}

Or advance user use the functionality of C#2.0 using yield keyword

private IEnumerable<ListItem> GetSelectedItems(ListControl listControl)
{
    foreach (ListItem itm in listControl.Items)
    {
        if (itm.Selected)
            yield return itm;
    } 
}
.
.
.
.
.
.
{
    IEnumerable<ListItem> selectedList = new List<ListItem>();
    selectedList = GetSelectedItems(cbList);
}

And now using LINQ C#3.0

var selectedItems =
    from li in cbList.Items.Cast<ListItem>()
    where li.Selected == true
    select li;

The only difference is the Cast, we need to make ListItemCollection generic so we can use it in LINQ query.

And now lets see using Lambda Expression C#3.0

List<ListItem> selectedItems =
    cbList.Items.Cast<ListItem>()
    .Where(item => item.Selected == true).ToList();

Here also we did cast to get the extension method Where. And in Where condition we write the lambda expression instead of anonymous delegate function.

We can even write extension method on base class ListControl so we can have selected items for all the list control. Lets write one extension method on ListControl

public static List<ListItem> SelectedItems(this ListControl lc)
{
    List<ListItem> selectedItems =
                lc.Items.Cast<ListItem>()
                .Where(item => item.Selected == true).ToList();
    return selectedItems;
}

And get the selected items using our extension method we will just call the SelectedItems method.

CheckBoxList cbList = new CheckBoxList();
cbList.SelectedItems();

How to get Property list only of Derived class?

Hi All,

Recently I seen one requirement on ASP.Net Forum, some one posted question about How to get property list of Derived class.

Generally if we want to get all the property of class we use GetProperties() method. Which gives you the PropertyInfo[]. Lets see with example. We have following class Hierarchy.

class A
{
    public int Id { get; set; }

    public virtual void PrintMe()
    {
        Console.WriteLine("Id : " + Id);
    }

}

class B : A
{
    public string Name { get; set; }
    public string Address { get; set; }
    public override void PrintMe()
    {
        base.PrintMe();
        Console.WriteLine("Name : " + Name);
    }
}

In A there is only one property and in B we have 2 more property, our goal to get only two property declared in class B. Lets check with GetProperties().

PropertyInfo[] properties = typeof(B).GetProperties(); 

Console.WriteLine(properties.Count()); //will print 3

You can see its printing 3, that means it consider properties which are in parent and child too. There is one overload of GetProperties() which has BindingFlags, but with that also we are not achieving our goal.

So here is the solution using LINQ.

var prop =
    from
        p in typeof(B).GetProperties()
    where p.DeclaringType == typeof(B)
    select p;

Console.WriteLine(prop.Count()); //Will print 2

We are filtering on DeclaringType; which should be typeof(B). You can see now its returning 2, that what we needed.

Finding occurrences of character in string [LINQ]

Hello All,

This is normal requirement when you need to find the occurrence of single character with in the string. Here is the example which uses LINQ to achive this.

string strString = "AA BRA KA DABRA";

var grp = from c in strString.ToCharArray() 
        group c by c into m
        select new { Key = m.Key, Count = m.Count() };

foreach (var item in grp)
{
    Console.WriteLine(
        string.Format("Character:{0} Appears {1} times", 
        item.Key.ToString(), item.Count));
}

Creating Rad Menu using LINQ

Hello all,

I created sample code to generate Dynamic RadMenu with use of LINQ. The controls like RadMenu, AspMenu which accept XML as the datasource to generate the output.

I use LINQ to generate XML, you can read here to generate XML using LINQ.

You can find the simple project at Code Project. You can modify the code with your use, its dynamically creates the node also it uses the recursion to generate n level menu item.

Best way to generate XML using LINQ

Hi all,

As we know C# 3.0 with LINQ gives us lots of power and smart coding, here is one more example which uses features of LINQ to generating XML of the abstract type.

I will give you example where you need to display the person details along with the address, here address can be anything like home address, email address, IM address ...

Here are the classes which require to achive our goal.

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<Address> Address = new List<Address>();
}
 
class Address
{
    public string AddressValue { get; set; }
    public string Type { get; set; }
}

Person class used to store person data, and Address class which holds the type of address.
Now lets add few details.

Person Person1 = new Person() { FirstName = "Imran", LastName = "Bhadelia" };
Person1.Address.Add(new Address() { Value = "imran.bhadelia[at]gmail.com", Type = "Email" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran[at]gmail.com", Type = "AlternativeEmail" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran[at]gmail.com", Type = "MSNInstanceMessanger" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran", Type = "YahooInstanceMessanger" });
 
Person Person2 = new Person() { FirstName = "Armaan", LastName = "Bhadelia" };
Person2.Address.Add(new Address() { Value = "armaan.bhadelia[at]gmail.com", Type = "Email" });
Person2.Address.Add(new Address() { Value = "Sweet Home, 406 Paradise Palace...", Type = "Residence" });
Person2.Address.Add(new Address() { Value = "bhadelia.armaan[at]gmail.com", Type = "MSNInstanceMessanger" });
Person2.Address.Add(new Address() { Value = "bhadelia.armaan", Type = "YahooInstanceMessanger" });
 
List<Person> PersonList = new List<Person>();
PersonList.Add(Person1);
PersonList.Add(Person2);

Here I used one feature of C#3.0, which is Object Initialization Expressions. See the constructor of both class, Its default and while creating object I am setting the value to the fields of class [Smart coding]. I added this two class into List variable, from where LINQ will generate XML which looks like...

<Persons>
    <Person FirstName="Imran" LastName="Bhadelia">
        <Address Email="imran.bhadelia[at]gmail.com" /> 
        <Address AlternativeEmail="bhadelia.imran[at]gmail.com" /> 
        <Address MSNInstanceMessanger="bhadelia.imran[at]gmail.com" /> 
        <Address YahooInstanceMessanger="bhadelia.imran" /> 
    </Person>
    <Person FirstName="Armaan" LastName="Bhadelia">
        <Address Email="armaan.bhadelia[at]gmail.com" /> 
        <Address Residence="Sweet Home, 406 Paradise Palace..." /> 
        <Address MSNInstanceMessanger="bhadelia.armaan[at]gmail.com" /> 
        <Address YahooInstanceMessanger="bhadelia.armaan" /> 
    </Person>
</Persons>

And now LINQ to generate above XML.

var PersonXml =
    new XElement("Persons",
        from person in PersonList
        select (new XElement("Person",
            new XAttribute("FirstName", person.FirstName),
            new XAttribute("LastName", person.LastName),
 
            from addr in person.Address
            select new XElement("Address", new XAttribute(addr.Type, addr.Value)))));

Console.WriteLine(PersonXml.ToString());

How it works??
Line#2: Create the main element which is Persons
Line#3: Getting single Persons object from PersonList
Line#4: Creating sub element to Persons which is Person
Line#5&6: Creating attributes to show person details.
Line#8: Getting Address of the person from Address collection
Line#9: Creating Address element along with the attribute

Easy? I found its very easy :)

Now if you want to add this information into SQL Server 2005 and higher, you can pass it as string and in your procedure grab the value. There are lots of way to grab value from XML in SQL, you can check this url, it has almost all the operations to XML datatype in SQL.

I created SQL script for specific to this xml.

SELECT 
    Person.value('@FirstName[1]', 'VARCHAR(100)') as FirstName,
    Person.value('@LastName[1]', 'VARCHAR(100)') as LastName,
    Person.value('(Address/@Email)[1]', 'VARCHAR(100)') as Email,
    Person.value('(Address/@AlternativeEmail)[1]', 'VARCHAR(100)') as AlternativeEmail,
    Person.value('(Address/@Residence)[1]', 'VARCHAR(100)') as Residence,
    Person.value('(Address/@MSNInstanceMessanger)[1]', 'VARCHAR(100)') as MSNInstanceMessanger,
    Person.value('(Address/@YahooInstanceMessanger)[1]', 'VARCHAR(100)') as YahooInstanceMessanger
FROM @Persons.nodes('/Persons/Person') p(Person)

Remove duplicate comma from list

Hi,

Recently on ASP.NET forum there was post for removing duplicate ',' from a comma saperated string.

This is using Regex

string abodes = ",House,,,,Apartment,,,Villa,,,,Townhouse,,,,";
Regex regx = new Regex(@",+");
abodes = regx.Replace(abodes, ",").Trim(',');

And now let's see using LINQ

string abodes = ",House,,,,Apartment,,,Villa,,,,Townhouse,,,,"; 
string cleanAbodes = string.Join(",", abodes.Split(',').Where(a => a != string.Empty).ToArray());

Have Fun with LINQ

101 LINQ Samples

Samples @delicious

Samples @microsoft