Solved

Writing elegant LINQ queries using lamda expressions

Posted on 2010-08-25
15
617 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 63

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
 
LVL 63

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 63

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 63

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 63

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

832 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