How to do a LINQ query for count of entity whose owner == current user?

Question

I'm using Microsoft Dynamics CRM 2011 and ADX Studios. On one of the pages I'm trying to make a widget that will display the user's number of current leads. I want to do a LINQ query that selects the count of lead where the owner of the lead entity in the CRM database is equal to the current user. I'm new to LINQ, so I'm still trying to get a grasp on the semantics of the queries. Below I have my code so far which pulls in all leads. I'm not quite sure how to work in the "where" clause that checks to see if the owner id equals that of the current user.

count = context.LeadSet.ToList().Count();

How do you get the current user credentials? Would it be `count = context.LeadSet.Count(lead => lead.OwnerId == currentUserId);` ? You don't need to call `ToList` in order to count the number of entities. `ToList` will load all your data into memory, which is far from optimal, while `Count` extension method would translate lambda predicate into SQL, which will return only one number. — Ilya Ivanov, Oct 28 '13 at 17:51
The user credentials are entered via ADFS when the site is first loaded. I can't quite seem to get the correct way to get the currentUserID. Your code looks like it may work, but it keeps telling me it cannot resolve "currentUserID". Also as a side note, I used ".ToList()" because I was receiving errors using just ".Count" and found on https://crmconsultancy.wordpress.com/2011/06/06/using-linq-in-crm-2011-plugins/ that CRM 2011 has issues with ".Count" unless ".ToList" or .AsEnumerable(). — Matt Healey, Oct 28 '13 at 18:29

score 6 · Answer 1 · edited May 23 '17 at 11:50

6

See this answer:

Linq to CRM doesn't support any aggregate expressions, so it would be better from a performance standpoint to use Fetch XML.

edited May 23 '17 at 11:50

Community

1
1

answered Oct 28 '13 at 19:15

Daryl

18,592
9
78
145

Really important that everyone who finds this (it ranks well on google!) understands why this is a better answer. The link provided does a good job of explaining. – glosrob Apr 05 '16 at 16:14

Matt Healey · Accepted Answer · 2013-12-20T15:35:57.827

One of the main issues was that the query also had to include the owner of the lead. This was a custom field added to the crm. Therefore the XRM file that is used by ADX Studios had to be rebuild with the CrmSvcUtil. I used the following batch file which can be altered depending on your project:

cd\"Program Files (x86)\Adxstudio\XrmPortals\6.0.0009\Framework"

CrmSvcUtil.exe /codeCustomization:"Microsoft.Xrm.Client.CodeGeneration.CodeCustomization, Microsoft.Xrm.Client.CodeGeneration" /url:https://contoso.com/XRMServices/2011/Organization.svc /username:user /password:password /out:"C:\Xrm.cs" /namespace:Xrm /servicecontextprefix:Xrm /servicecontextname:XrmServiceContext

pause

This must be run as administrator to complete making the new XRM File. That file must then be copied and pasted into the ADX Studio solution. Rebuild the solution and your custom fields will now be used by intellisense when you do your query.

var context = new XrmServiceContext();
var leadList = (from a in context.LeadSet
                       where a.customFieldOwnerId.Id == Id
                       select a).ToList().Count();
                   int count = leadList;

To get a count when using Microsoft Dynamics CRM you must use .ToList() before .Count(). It may seem redundant, but you will get an error if you do not.

It is worth noting the impact of ToList() here - especially for an entity like Lead. I would recommend anyone who finds this answer looks at the answer given by @daryl. — glosrob, Apr 05 '16 at 16:13

score -1 · Answer 3 · answered Oct 28 '13 at 17:53

-1

I'm not sure your the data structure of LeadSet but i'd imagine something like:

context.LeadSet.Count(ls => ls.OwnerID == ownerId);

This above pulls the count of all leads where the OwnerId equals your parameter ownerId.

If you want the objects that match, just use .Where in place of .Count

answered Oct 28 '13 at 17:53

gleng

6,185
4
21
35

1

@nycdan yes. [Count overload with Func predicate](http://msdn.microsoft.com/en-us/library/bb535181.aspx) – Ilya Ivanov Oct 28 '13 at 17:57
1

Thanks. I've always done the .where followed by the .count(). Good to see another approach. – nycdan Oct 28 '13 at 18:04

score -3 · Answer 4 · answered Oct 28 '13 at 17:56

try this using lambda expression

int count = context.LeadSet.Where(a=>a.currentUserName==currentUserName").Count();

or linq query syntax

int count=(from item in context.LeadSet.ToList()
           where item.currentUserName==currentUserName
           select item).count();

How to do a LINQ query for count of entity whose owner == current user?

4 Answers4