Solved

Writing elegant LINQ queries using lamda expressions

Posted on 2010-08-25
15
612 Views
Last Modified: 2013-11-11
Can someone help me out in writing this query using a more elegant approach?  Although this query works, I would like to neaten/shorten it - using Lamda expressions if possible, except my Lamda knowledge is a little limited.

I am using Sitefinity to develop this application, and this query involves some custom tables and 2 Sitefinity built-in tables.
public static IQueryable GetUserDetailsById(Guid userID)
        {
            //Create an instance of the TicketRequestDataContext object.
            TicketRequestDataContext db = new TicketRequestDataContext();

            IQueryable userDetails = from u in db.telerik_UserMetaInfos
                                     where u.UserId == userID
                                     select new
                                     {
                                         FirstName = (from f in db.telerik_UserMetaInfos
                                                      where f.PropertyName == "FirstName"
                                                      select f.Text),
                                         LastName = (from l in db.telerik_UserMetaInfos
                                                     where l.PropertyName == "LastName"
                                                     select l.Text),
                                         Email = (from e in db.telerik_Users
                                                  where e.UserId == userID
                                                  select e.Email).Single(),
                                         JobTitle = (from j in db.telerik_UserMetaInfos
                                                     where j.PropertyName == "JobTitle"
                                                     select j.Text),
                                         DirectPhone = (from d in db.telerik_UserMetaInfos
                                                        where d.PropertyName == "DirectPhone"
                                                        select d.Text),
                                         MobilePhone = (from m in db.telerik_UserMetaInfos
                                                        where m.PropertyName == "MobilePhone"
                                                        select m.Text),
                                         FaxNumber = (from f in db.telerik_UserMetaInfos
                                                      where f.PropertyName == "FaxNumber"
                                                      select f.Text),
                                         Address1 = (from a in db.telerik_UserMetaInfos
                                                     where a.PropertyName == "Address1"
                                                     select a.Text),
                                         Address2 = (from a in db.telerik_UserMetaInfos
                                                     where a.PropertyName == "Address2"
                                                     select a.Text),
                                         City = (from c in db.telerik_UserMetaInfos
                                                 where c.PropertyName == "City"
                                                 select c.Text),
                                         Country = (from c in db.telerik_UserMetaInfos
                                                    where c.PropertyName == "Country"
                                                    select c.Text),
                                         Postcode = (from c in db.telerik_UserMetaInfos
                                                     where c.PropertyName == "Postcode"
                                                     select c.Text),

                                     };

            return userDetails;
        }

Open in new window

TicketRequestDataContext.PNG
telerik-UserMetaInfo.PNG
0
Comment
Question by:markerasmus
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 27

Expert Comment

by:nmarun
ID: 33520745
Here's what I've been able to gather from your requirements.

1. You need to put the userId condition on every property (FirstName, LastName, Email, etc.,)

2. You need to use First() or FirstOrDefault() on each of the sub-linq statements.

A snippet looks like below. FYI, I've not tried this anywhere.

Arun



IQueryable userDetails = from u in db.telerik_UserMetaInfos

                         where u.UserId == userID

                         select new

                         {

                            FirstName = (from f in db.telerik_UserMetaInfos

                                         where f.PropertyName == "FirstName"

                                            && f.UserId = userID

                                         select f.Text).FirstOrDefault(),

                            LastName = (from l in db.telerik_UserMetaInfos

                                        where l.PropertyName == "LastName"

                                            && l.UserId = userID

                                        select l.Text).FirstOrDefault()

                         };

Open in new window

0
 

Author Comment

by:markerasmus
ID: 33520859
You are quite right, the query should look this this.

It still seems like a pretty long-winded way of doing it.  Is this the correct approach?
IQueryable userDetails = from ud in db.telerik_UserMetaInfos
                                     where ud.UserId == userID
                                     select new
                                     {
                                         FirstName = (from u in db.telerik_UserMetaInfos
                                                      where u.PropertyName == "FirstName"
                                                      && u.UserId == userID
                                                      select u.Text).First(),
                                         LastName = (from u in db.telerik_UserMetaInfos
                                                     where u.PropertyName == "LastName"
                                                     && u.UserId == userID
                                                     select u.Text).First(),
                                         Email = (from u in db.telerik_Users
                                                  where u.UserId == userID
                                                  select u.Email).First(),
                                         JobTitle = (from u in db.telerik_UserMetaInfos
                                                     where u.PropertyName == "JobTitle"
                                                     && u.UserId == userID
                                                     select u.Text).First(),
                                         DirectPhone = (from u in db.telerik_UserMetaInfos
                                                        where u.PropertyName == "DirectPhone"
                                                        && u.UserId == userID
                                                        select u.Text.First()),
                                         MobilePhone = (from u in db.telerik_UserMetaInfos
                                                        where u.PropertyName == "MobilePhone"
                                                        && u.UserId == userID
                                                        select u.Text).First(),
                                         FaxNumber = (from u in db.telerik_UserMetaInfos
                                                      where u.PropertyName == "FaxNumber"
                                                      && u.UserId == userID
                                                      select u.Text).First(),
                                         Address1 = (from u in db.telerik_UserMetaInfos
                                                     where u.PropertyName == "Address1"
                                                     && u.UserId == userID
                                                     select u.Text).First(),
                                         Address2 = (from u in db.telerik_UserMetaInfos
                                                     where u.PropertyName == "Address2"
                                                     && u.UserId == userID
                                                     select u.Text.First()),
                                         City = (from u in db.telerik_UserMetaInfos
                                                 where u.PropertyName == "City"
                                                 && u.UserId == userID
                                                 select u.Text).First(),
                                         Country = (from u in db.telerik_UserMetaInfos
                                                    where u.PropertyName == "Country"
                                                    && u.UserId == userID
                                                    select u.Text.First()),
                                         Postcode = (from u in db.telerik_UserMetaInfos
                                                     where u.PropertyName == "Postcode"
                                                     && u.UserId == userID
                                                     select u.Text).First(),
                                     };

Open in new window

0
 
LVL 16

Expert Comment

by:kris_per
ID: 33521290

This is where Pivot is to be used. Pivot is to transform row values into columns; in your case values of PropertyName field (FirstName, LastName, etc) has to become columns/fields. T-SQL has built-in PIVOT function; But currently LINQ does not have built-in pivot feature (I think)...But the following links does some kind of pivoting:

This link provides LINQ extension method for pivot => http://www.reflectionit.nl/Blog/PermaLinke3b1a10d-cd24-4810-a4bf-a4285b74dbb6.aspx

http://stackoverflow.com/questions/963491/pivot-data-using-linq

So pivoting is the way for this...

0
 
LVL 27

Expert Comment

by:nmarun
ID: 33521314
Depending upon your answer based on the below articles, I would change First() to FirstOrDefault().

http://stackoverflow.com/questions/1024559/when-to-use-first-and-when-to-use-firstordefault-with-linq
http://codeforeternity.com/blogs/technology/archive/2008/04/25/linq-first-and-firstordefault-methods.aspx

Basically, if you're SURE that there's at least one element, use First(). If not, use FirstOrDefault().

And this is the only way I can think of doing this.

Arun
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 475 total points
ID: 33521681
Hi markerasmus;

See if this fits your needs.

Fernando
public static IQueryable GetUserDetailsById(Guid userID)

{

    //Create an instance of the TicketRequestDataContext object.

    TicketRequestDataContext db = new TicketRequestDataContext();



    IQueryable userDetails = (from u in db.telerik_UserMetaInfos

                              where u.UserId == userID

                              group u by u.UserId into ug

                              select new

                              {

                                  FirstName = ug.Where( fn => fn.PropertyName == "FirstName").Select( fn = fn.Text).First(),

                                  LastName = ug.Where( ln => ln.PropertyName == "LastName").Select( ln = ln.Text).First(),

                                  Email = db.telerik_Users.Where( u => u.UserId == userID).Select( u = u.Email).First(),

                                  JobTitle = ug.Where( j => j.PropertyName == "JobTitle").Select( j = j.Text).First(),

                                  DirectPhone = ug.Where( d => d.PropertyName == "DirectPhone").Select( d = d.Text).First(),

                                  MobilePhone = ug.Where( m => m.PropertyName == "MobilePhone").Select( m = m.Text).First(),

                                  FaxNumber = ug.Where( f => f.PropertyName == "FaxNumber").Select( f = f.Text).First(),

                                  Address1 = ug.Where( a => a.PropertyName == "Address1").Select( a = a.Text).First(),

                                  Address2 = ug.Where( a => a.PropertyName == "Address2").Select( a = a.Text).First(),

                                  City = ug.Where( c => c.PropertyName == "City").Select( c = c.Text).First(),

                                  Country = ug.Where( c => c.PropertyName == "Country").Select( c = c.Text).First(),

                                  Postcode = ug.Where( c => c.PropertyName == "Postcode").Select( c = c.Text).First()

                              }).FirstOrDefault();



    return userDetails;

}

Open in new window

0
 

Author Comment

by:markerasmus
ID: 33521819
Hi (again!) Fernando,

I am getting a compliation error on all the .Select(fn = fn.Text).First() on each line

e.g. "fn does not exist in the current context."
0
 
LVL 21

Expert Comment

by:naspinski
ID: 33521856
OP, your code really doesn't make any sense to me.  First you are looking for a
from u in db.telerik_UserMetaInfos
                                     where u.UserId == userID
which means you are looking for a particular user, hence you got the 'u'

But then in your select, you never use that 'u' at all, instead you do a bunch of other queries.  And they are queries that return IEnumerable (collections) of what I assume is strings:

FirstName = (from f in db.telerik_UserMetaInfos
                                                      where f.PropertyName == "FirstName"
                                                      select f.Text)

is returnnin ALL of the telerik_UserMetaInfo items that PropertyName == "FirstnName" not just the ones where u.UserId == userId.

I think you need to take a look at your original code as I assume it really isn't working as you would like.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 475 total points
ID: 33521895
Sorry, that should be :

Select(fn => fn.Text).First()

=> is lambda expression

Fernando
0
 

Author Comment

by:markerasmus
ID: 33522004
Fernando:  that fixed that issue, but now it errors on }).FirstOrDefault(); - "Cannot implicitly convert type "AnonymousType#1' to System.Linq.IQueryable.  

If I remove ).FirstOrDefault() it compiles, or if I change IQueryable to type var.  

Should I use var?
0
 
LVL 21

Assisted Solution

by:naspinski
naspinski earned 25 total points
ID: 33522118
You can use var if you want to return the anonymous type, or you can cast it into some Class in your select like this

IQueryable userDetails = (from u in db.telerik_UserMetaInfos
                              where u.UserId == userID
                              group u by u.UserId into ug
                              select new SomeObject()
                              {
                                  FirstName = ug.Where( fn => fn.PropertyName == "FirstName").Select( fn = fn.Text).First(),
                                  LastName = ...

//you just have to have 'SomeObject' defined somewhere if it already isn't
0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 475 total points
ID: 33522137
Hi markerasmus;

That is what happens when I answer a question without having my first cup of coffee.

Remove this part of the query, ".FirstOrDefault()", this converts the IQueryable to an AnonymousType. To leave it as an IQueryable just rermove it.

Fernando
0
 

Author Comment

by:markerasmus
ID: 33522167
Okay, I see.  Thanks!  Can someone recommend a good Lamda resource?
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33522467
Try this link:

Lambda Expressions (C# Programming Guide)
http://msdn.microsoft.com/en-us/library/bb397687.aspx
0
 

Author Closing Comment

by:markerasmus
ID: 33522859
The solution was exactly what I was looking for.  Thanks.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33522885
Not a problem, glad I was able to help.  ;=)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now