?
Solved

Writing elegant LINQ queries using lamda expressions

Posted on 2010-08-25
15
Medium Priority
?
655 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 64

Accepted Solution

by:
Fernando Soto earned 1900 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 64

Assisted Solution

by:Fernando Soto
Fernando Soto earned 1900 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 100 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 64

Assisted Solution

by:Fernando Soto
Fernando Soto earned 1900 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 64

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 64

Expert Comment

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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
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 is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 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