Solved

Writing elegant LINQ queries using lamda expressions

Posted on 2010-08-25
15
645 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

632 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