Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

Writing elegant LINQ queries using lamda expressions

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
markerasmus
Asked:
markerasmus
  • 5
  • 5
  • 2
  • +2
4 Solutions
 
nmarunCommented:
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
 
markerasmusAuthor Commented:
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
 
kris_perCommented:

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
nmarunCommented:
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
 
Fernando SotoCommented:
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
 
markerasmusAuthor Commented:
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
 
naspinskiCommented:
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
 
Fernando SotoCommented:
Sorry, that should be :

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

=> is lambda expression

Fernando
0
 
markerasmusAuthor Commented:
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
 
naspinskiCommented:
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
 
Fernando SotoCommented:
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
 
markerasmusAuthor Commented:
Okay, I see.  Thanks!  Can someone recommend a good Lamda resource?
0
 
Fernando SotoCommented:
Try this link:

Lambda Expressions (C# Programming Guide)
http://msdn.microsoft.com/en-us/library/bb397687.aspx
0
 
markerasmusAuthor Commented:
The solution was exactly what I was looking for.  Thanks.
0
 
Fernando SotoCommented:
Not a problem, glad I was able to help.  ;=)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now