markerasmus
asked on
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.
telerik-UserMetaInfo.PNG
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;
}
TicketRequestDataContext.PNGtelerik-UserMetaInfo.PNG
ASKER
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?
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(),
};
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...
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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."
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."
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
If I remove ).FirstOrDefault() it compiles, or if I change IQueryable to type var.
Should I use var?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, I see. Thanks! Can someone recommend a good Lamda resource?
Try this link:
Lambda Expressions (C# Programming Guide)
http://msdn.microsoft.com/en-us/library/bb397687.aspx
Lambda Expressions (C# Programming Guide)
http://msdn.microsoft.com/en-us/library/bb397687.aspx
ASKER
The solution was exactly what I was looking for. Thanks.
Not a problem, glad I was able to help. ;=)
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
Open in new window