| LINQ group by with multiple counts |
you should group by trackId only, if you want results by trackId...
var query2 = query
.Where(m => m.Source == "UserRef")
.GroupBy(m => m.TrackId)
.Select(g => new FullReferrer {
Customer = g.Key,
FullRefFalseCount = g.Count(x => !x.FullRef),
FullRefTrueCount = g.Count(x => x.FullRef)
});
|
| How do i make a count that counts the number of page-views? |
Best way is to use some js tracker like Google Analytics..
If you do not want to track page refresh, you can use cookie or session to
store timestamp of last time the increment happened, so you do not
increment till it expires.
|
| Count all elements, display results with unique values and counts |
Working fiddle
HTML
<div class="username">jsmith</div>
<div class="username">jsmith</div>
<div class="username">bruth</div>
<div class="username">bruth</div>
<div class="username">jsmith</div>
<div class="username">jsmith</div>
<div class="username">bdole</div>
<div class="username">bdole</div>
<br />
<div id="result"></div>
Javascript
var divs = []
$('.username').each(function() {
var uid = $(this).html();
if ($.inArray(uid, divs) < 0) {
divs.push(uid);
}
}).promise().done(function() {
$.each(divs, function(index, value) {
var sel = '.username:contains("' + value + '")';
var n = $(sel).length;
$('#result').append('<di
|
| Restructure view with multiple counts: output each count as one row, not one column |
Since the three counts appear to be entirely mutually exclusive, you can do
it like this:
CREATE OR REPLACE VIEW oa_count_ii AS
SELECT dl.count_type, count(ilv.count_type) record_count from
(SELECT CASE level
WHEN 1 THEN 'newapp_admuhflscp'
WHEN 2 THEN 'apfrvw_admuhflscp'
WHEN 3 THEN 'rvwtutw_admuhflscp'
END count_type
FROM DUAL
CONNECT BY level <= 3) dl
LEFT JOIN
(SELECT CASE
WHEN cap_udfi IN ('NEW_ENT','IRP_RVW')
AND mhd_mscc = 'ADMUHFLSCP'
THEN 'newapp_admuhflscp'
WHEN cap_udfi IN
('APF_RVW','RVW_RVW','TUT_RVW','TU2_RVW','TUT_DC1','TU2_DC1','RVW_IRP','IRP_IIO','IRP_DC1','IIO_DC1','RECALL')
AND cap_dec1 = 'No Decision'
AND mhd_mscc = 'ADMUHFLSCP'
|
| MS Access SQL Calculating values by date count |
First, to use count(), you need an aggregation. Second, you should be
learning proper join syntax. The rest is pretty obvious:
SELECT AssassinName, count(DateCompleted) as NumSuccesses,
(count(*) - count(DateCompleted)) as NumMisses
FROM Assassins join
Assignments
on Assassins.AssassinID = Assignments.AssassinID
group by AssassinName
|
| Oracle Select statement calculating count from two tables |
I expect the most efficient way would be to use UNION ALL to re-combine the
two tables and do the query exactly as you have it. For some reason I get
the feeling that's not what you want to hear:
SELECT
licensee.license_type_id,
COUNT(*) AS count_all,
COUNT(CASE WHEN licensee.citizen = 'US' THEN 1 ELSE NULL END) AS
count_a,
COUNT(CASE WHEN licensee.citizen = 'Other' AND licensee.flag = 'Y'
THEN 1 ELSE NULL END) AS count_b,
COUNT(CASE WHEN licensee.flag = 'N' THEN 1 ELSE NULL END) AS
count_c
FROM
(select 'US' as citizen, licensee_us.* UNION ALL SELECT 'Other' as citizen,
licensee_other.* ) as licensee
INNER JOIN license_type ON licensee.license_type_id = license_type.id
GROUP BY licensee.license_type_id;
You can try a bunch of sub
|
| LINQ Group by and "count-if" |
Found it ... ARGH !
when I can use "g" as the variable for my inner lambda expression inside
the group becuse it refers to the original group "g". so i changed
g=>g.isScheduled toi=>i.isScheduled
Items.GroupBy(
i => i.ItemID,
i => new { isScheduled = i.isScheduled },
(key, g) => new ItemStatistics()
{
ItemID = key,
ScheduledItems = g.Count(i=>i.isScheduled),
UnscheduledItems = g.Count(i=> !i.isScheduled)
}).ToList();
and now everything is okay
|
| Linq to SQL - Group By and Count |
Simply use the the Count method:
from buildinguser in db.GetTable<BuildingUser>()
join building in db.GetTable<Building>()
on buildinguser.ID_BUILDING equals building.ID
join user in db.GetTable<User>()
on buildinguser.ID_USER equals user.ID
group building by building.NAME into grpBuilding
select new
{
building = grpBuilding.Key,
users = grpBuilding.Count()
};
|
| Linq query with multiple count |
You have to group by two fields if you want it to work
something like:
var query = from a in table
where a.Country.Equals("USA")
group a by new {a.Product_brand, a.Country} into grp
select new
{
Product_brand = grp.key.Product_brand,
Country = grp.Key.Country,
Black = grp.Count(a => a.Black=="Yes"),
White = grp.Count(a => a.White=="Yes"),
Red = grp.Count(a=> a.Red=="Yes"),
Green = grp.Count(a=> a.Green=="Yes")
}
|
| Linq Age GroupBy Aggregate Count |
Check this:
// mock data
var data = new List<dynamic> {
new { Age = 0, Count = 6 },
new { Age = 1, Count = 6 },
new { Age = 2, Count = 7 },
new { Age = 3, Count = 5 },
new { Age = 4, Count = 5 },
new { Age = 5, Count = 20 },
new { Age = 6, Count = 5 },
new { Age = 7, Count = 5 },
new { Age = 8, Count = 5 },
new { Age = 9, Count = 5 },
new { Age = 10, Count = 5 },
};
var age = new {
ZeroToFive = data.Where(x => x.Age < 6).Sum(x =>
x.Count),
AboveSix = data.Where(x => x.Age >= 6).Sum(x =>
x.Count)
};
|
| Linq-select group by & count |
You should try this
var results = from a in db.Questionaires
group a by new { y = a.Begin.Year, m = a.Begin.Month, d =
a.Begin.Day}
into g
select new { Day = g.Key.d, Year = g.Key.y,
Month = g.Key.m, Count = g.Count(),
Date =
g.Select(d=>d.Begin).FirstOrDefault() };
|
| Simple LINQ count, or so I thought |
As others have said, your real code should be fine: it sounds like the
problem was only that you were trying to execute this in the debugger
instead of in normal code. Personally I'm always somewhat leery of trying
to take things too far in the debugger - it can be useful of course, but if
things behave unexpectedly, I'd always see whether the same code works as
part of a real program, rather than assuming there's something
fundamentally wrong with the approach. The debugger has to work under
rather different constraints than the normal compilation and execution
process.
Likewise, as others have said, it's better to use Any() than Count() >
0. However, cleaner yet is to use the overload of Any accepting a
predicate:
if (Model.Version.Any(model => model.revision > Model.revision)
|
| Linq Count based on Boolean |
You can try a direct translation of your explanation to LINQ, which would
look like this:
var totalByCode = data
.GroupBy(item => item.Code)
.ToDictionary(
g => g.Key
, g => g.Count(o => !o.Negative) - g.Count(o => o.Negative)
);
This produces a Dictionary<string,int> that maps the Code to the
corresponding count computed as the difference between non-negative and
negative occurrences.
|
| linq aggregated nested count |
First, build a recursive method to count the Inner objects inside a Inner
object including itself:
public static int Count(Inner inner)
{
var count = 1;
if (inner.Inners != null && inner.Inners.Any())
count += inner.Inners.Sum(x => Count(x));
return count;
}
Then you can order:
var result = outers.OrderBy(o => o.Inners.Sum(i => Count(i)));
|
| Aggregate distinct count in LINQ - VB.NET |
Since PagedDataSource doesn't implement IEnumerable<T>, just
IEnumerable, you need to first use Cast to cast the sequence to a sequence
of the proper type. You'll need to know what the actual type of the
objects in the sequence are as you haven't mentioned what it is here.
Then just apply each operation one after the other:
(In C#:)
var query = table.Cast<YourTypeGoesHere>()
.Select(item => item.Column)
.Distinct()
.Count();
|
| Linq Group and Count in IEnumerable |
Your two queries are returning different data, the first is returning items
of type ItemDetail, while the second query is returning items of an
anonymous type.
If you want an IEnumerable of an anonymous type, you will need to declare
it using the var keyword, i.e.
var itemdetails = (from c in db.CLIENTDETAILS
join l in db.LOCATIONS on c.CLIENTNUMBER equals
l.CLIENTNUMBER
where c.CLIENTNUMBER == clientNumber
join i in db.ITEMDETAILS on l.LOCNUMBER equals
i.LOCNUMBER
where i.LOCNUMBER == l.LOCNUMBER
select i).GroupBy(it => it.DESC).Select(grp
=> new {DESC = grp.key, Count = grp.COUNT()}).OrderBy(x => x.DESC)
|
| Datatable Linq GroupBy, Aggregate Count |
try like this:
var hospital =
from hosp in tblClaimsMain.AsEnumerable()
group hosp by
new{CODE=hosp["CODE"],NAME=hosp["NAME"],REGION=hosp["REGION"]} into grp
select new
{
ProviderCode = grp.Key.CODE,
ProviderName = grp.Key.NAME,
Region = grp.Key.REGION,
MONTH1 = grp.Count(g => g["MONTH"].ToString() == "1"),
IP1 = grp.Count(g =>g["MONTH"].ToString() == "1" &&
g["COVCODE"].ToString() == "IP"),
OP1 = grp.Count(g =>g["MONTH"].ToString() == "1"&&
g["COVCODE"].ToString() == "OP"),
MONTH2 = grp.Count(g => g["MONTH"].ToString() == "2"),
IP2 = grp.Count(g =>g["MONTH"].ToString() == "2" &&
g["COVCODE"].ToString() == "IP"),
OP2 = grp.Count(g =>
|
| Multi-Group Column Count Linq |
Try this.... (off the top of my head)
var result = from MyObjs in MyList
group MyObjs by new { MyObjs.A, MyObjs.B } into g
select new { g.Key.A, g.Key.B, MyCount = g.Count() }
Or if you prefer...
var result = MyList.GroupBy(x => new {x.A, x.B})
.Select(g => new {g.Key.A, g.Key.B, MyCount =
g.Count()});
|
| Linq Group by Month, Aggregate count |
Here's an example that produces your desired result
The core of it is this:
Group the elements by their Code properties
Group the elements in each grouping by their Color properties
Process each grouping, creating the result where each MonthX property is
set to the count of the objects within the inner grouping (items by code by
color) having the given month identifier
This handles specifically the 5 month values provided in the question, you
can either split out all month values that you like into their own
properties on your result object or build that out to a dictionary of month
index versus count if other values are required.
public enum Number
{
One = 11111,
Two = 22222,
Three = 33333,
Four = 44444,
Five = 55555
}
public class Data
{
public Number Code
|
| Using LINQ to count values in a multiple join |
You are doing a count on the column instead of the result. Try moving the
count:
var plant = (from c in db.CLIENTDETAILS
join l in db.LOCATIONS on c.CLIENTNUMBER equals l.CLIENTNUMBER
where c.CLIENTNUMBER == clientNumber
join i in db.ITEMDETAILS on l.LOCNUMBER equals i.LOCNUMBER
where i.LOCNUMBER == l.LOCNUMBER
join p in db.PLANT on i.CODE equals p.CODE
select p.DESCRIPTION).Count();
You probably can get rid of the where i.LOCNUMBER == l.LOCNUMBER as well as
it is already taken care of in the join, so your query would become:
var plant = (from c in db.CLIENTDETAILS
join l in db.LOCATIONS on c.CLIENTNUMBER equals l.CLIENTNUMBER
where c.CLIENTNUMBER == clientNumber
join i in db.ITEMDETAILS on l.LOCNUMBER equals i.LOCNUMBER
join
|
| Linq Count Unique Values in List |
This will sum up using both the ItemID and ItemDescription values:
var x = from order in orders
from item in order.OrderItems
group item by new { ItemID = item.itemID, ItemDescription =
item.itemDescription } into g
select new { ItemID = g.Key.ItemID, ItemDescription =
g.Key.ItemDescription, Count = g.Sum(o => o.itemQuantity) };
|
| System.Linq.Dynamic library - need Count() example |
If your aim is to retrieve the count of rows in testTable for each value of
Field1, then I would have thought you want to firstly GroupBy Field1, then
Select afterwards. So basically reverse the order of your Select and
GroupBy and remove the Count() from the GroupBy.
|
| How to cache .Count() queries in NHibernate.Linq? |
Adding Cacheable before the count will cause the aggregate count results to
be cached. This can be seen by the SQL generated from my example below.
Domain and Mapping Code
public class Entity
{
public virtual long id { get; set; }
public virtual string name { get; set; }
}
public class EntityMap : ClassMap<Entity>
{
public EntityMap()
{
Id(x => x.id).GeneratedBy.Identity();
Map(x => x.name);
Cache.ReadOnly();
}
}
The test code itself.
using (var session = NHibernateHelper.OpenSession())
using (var tx = session.BeginTransaction())
{
session.Save(new Entity() { name = "Smith" });
session.Save(new Entity() { name = "Smithers" });
session.Save(new Entity() { name = "Smithery" });
session.Save(new Entity() { name = "S
|
| Calculating a running count & running total across customers with SQL |
You should use ROW_NUMBER (link) instead of COUNT:
DECLARE @Threshold NUMERIC(19,2)=1000; -- Use the same data type as
`[AMT]`'s data type
Select
[DID]
, [AMT]
, [Gf_Date]
--, COUNT([GID]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date])
[RunningGift_Count]
, ROW_NUMBER() OVER (PARTITION BY [DID] ORDER BY [Gf_Date])
[RunningGift_Count]
, SUM([AMT]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [CumlativeTotal]
, CASE
WHEN SUM([AMT]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) >=
@Threshold THEN 1
ELSE 0
END IsThresholdPassed
FROM [dbo].[MCT]
WHERE [SC] is null
ORDER BY [DID]
|
| Linq to SQL Left Join, Order and Group By Count |
Try this:
from b in db.Buildings
join u in db.BuildingUsers on b.ID equals u.ID_BUILDING into g
orderby g.Count() descending, b.Name descending
select new
{
Id = b.ID,
Name = b.NAME,
Total = g.Count()
}
|
| Linq query with Include to count joined records |
Try this. This gets the date from StartDate by using the .Date property.
And it names the result of the Count() call as "StudentCount".
select new { ev.EventID, ev.StartDate.Date, ev.Title, StudentCount =
ev.EventStudents.Count() }
|
| LINQ in LinqPad count how many times a word shows up |
I'm not sure there's a Linq-To-SQL/Entities way to complete this, since SQL
doesn't do this easily.
var result = (from d in IPACS_Documents
join dp in IPACS_ProcedureDocs on d.DocumentID equals dp.DocumentID
join p in IPACS_Procedures on dp.ProcedureID equals p.ProcedureID
where d.DocumentID == 4
&& d.DateDeleted == null
select d.Html).First();
int count = Regex.Matches(result, "<p>|</p>").Count;
will probably do it.
|
| How to count the number of elements that match a condition with LINQ |
int divisor = AllMyControls.Where(p =>
p.IsActiveUserControlChecked).Count()
or simply
int divisor = AllMyControls.Count(p => p.IsActiveUserControlChecked);
Since you are a beginner, it would be worthwhile to take a look at
Enumerable documentation
|
| Finding Average of value based on count of rows in LINQ |
You could just use average:
var average = (from a in db.Reviews
where a.ReviewsRestaurantID.Equals(id)
select a.ReviewsRating).Average();
You could also do it with aggregate like this (if what you really want to
do is more complicated, here is an example implementation of average):
var result = db.Reviews.Where(r => r.ReviewsRestaurantID = id)
.Aggregate(new () { total = 0, count = 0, avg = 0.0 },
(o, n) => {
var result = new () { total = o.total+n.ReviewsRating, count =
o.count+1, avg = 0.0};
result.avg = result.total / result.count;
return result;
}, (r) => r.avg);
|
| Linq to Entities - is count() clever enough to send a group by clause to the DB |
Yes, the group by and count will be included in the generated SQL.
Your query can be a bit more succinct: _db.Foo.Count(f => f.Bar > 3)
will do the same thing.
I recommend using LNQPad for testing out your queries and improving your
LINQ skills.
|
| LINQ Entity Framework 4 SELECT COUNT LEFT OUTER JOIN IN GROUP BY |
I think you have to change the grouping source here:
group new { parent, jn } by parent.parentId
into g
select new {count = g.Select(x => x.jn).Count(), parentId =
g.Key};
|
| linq count error: DbExpressionBinding requires an input expression with a collection ResultType. Parameter name: input |
you should use 'by new' after 'group'. I hope this will help you.
var entries = from entry in _db.Entries
group entry by new { entry.Name } into groupedByName
select new
{
groupedByName.Key.Name,
NumberOfComments = groupedByName.Count(x => x.Name != null)
};
|
| How do I count the number of rows that have a value in column "A", but have a different value in column "B" using Linq |
var c = DataTableA.AsEnumerable()
.GroupBy(r=>r.Field<int>("ID_F"))
.Where(g=>g.Count()>1 &&
g.GroupBy(r=>r.Field<int>("ID_UEH"))
.Where(a=>a.Count()>1).Count() > 1)
.Count();
|
| Disqus count.js returns the wrong comment count and reaction count |
Specify the absolute URL associated with that thread in your comment count
href link instead of the relative url which you are currently using. The
absolute URL should would be:
<a
href="http://in-the-attic.com/2013/01/04/building-a-blog-using-jekyll-bootstrap-and-github-pages-a-beginners-guide/">Comments</a>
If you are still not seeing the correct count display for that particular
thread, you can use an identifier:
<a
href="http://in-the-attic.com/2013/01/04/building-a-blog-using-jekyll-bootstrap-and-github-pages-a-beginners-guide/"
data-disqus-identifier="EXAMPLE">Comments</a>
Keep in mind that you cannot use the identifier until you have set one for
that thread within the Javascript configuration variables
|
| LINQ to SQL exception: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator |
The error message says "except the contains operator." Have you considered
using the Contains operator? It should do the same thing.
So from
IEnumerable<BookingType> bookingsToDelete =
db.GetTable<BookingType>().Where(b => bookings.Any(p => p.Pren
== b.Pren && p.ReservationCode == b.ReservationCode));
to
IEnumerable<BookingType> bookingsToDelete =
db.GetTable<BookingType>().Where(b => bookings.Contains(p =>
p.Pren == b.Pren && p.ReservationCode == b.ReservationCode));
I realise that the list wont contain the same objects so you may need to do
something like:
bookings.Select(booking => booking.PrimaryKeyOfAwesome).Contains(b =>
b.PrimaryKeyOfAwesome) etc etc.
Edited for clarity
Edit for humility
Ok, so after actually re
|
| Rio fieldbinding: LINQ interface not supporterd error on Datetime in LINQ comparison? |
The only things I can think that might be causing this are:
If this is some kind of linker bug where Xamarin.Android is removing the
comparable support for DateTime before deploying to the Android test device
If this is some kind of compiler difference where Xamarin.Android is
somehow picking up the base INotifyChange interface for Value which uses
object instead of the INotifyChange<T> interface which uses new to
override Value to type T.
To test if it's the first, can you deploy in debug with the linker set to
'none' - does the problem still occur?
If it's the first, then you can trick the linker into including this
interface using a file like LinkerPleaseInclude.
To test if it's the second, can you change your code to:
mylist.Where(f => ((DateTime)f.LastSeen.Value
|
| LINQ to Entities does not recognize the method 'System.Linq.IQueryable` |
You could just select the Id and after it create your own anonymous object
using linq to objects, for sample:
var model = _db2.Persons.Select(x => x.Id)
.ToList() // return int[]
.Select((id, index) => new
{
rn = index + 1,
col1 = id
}) // return anonymous[] (with rn and
col1)
.AsEnumerable(); // get an IEnumerable (readonly
collection)
Problably this is happen because Entity Framework does not support this
kind of query using linq as linq could do in memory, so, in this case, you
could select just you need (id in your case) and execute it, using ToList()
method to concretiz
|
| C# Linq CopyToDataTable based of Linq To Sql table class structure |
The error occurs because you are trying to implicitly cast
IEnumerable<TableA> to IEnumerable<DataRow>.
What you need to do is create a new DataTable and add rows to it (DataRow
can not exist without a DataTable).
Here's an example (untested, but you get the idea):
var name = new DataColumn();
name.DataType = typeof(string);
name.ColumnName = "Name";
var surName = new DataColumn();
surName.DataType = typeof(string);
surName.ColumnName = "surName";
var concatName = new DataColumn();
concatName.DataType = typeof(string);
concatName.ColumnName = "concatName";
var id = new DataColumn();
id.DataType = typeof(int);
id.ColumnName = "ID";
var table = new DataTable("TableA");
table.Columns.Add(name);
table.Columns.Add(surName);
table.Columns.Add(concatName);
table.Columns.Add(id)
|
| Specific linq exception when converting string to int. LINQ to Entities does not recognize the method 'Int32 ToInt32(System.Object)' m |
You can easily fix this by first performing the conversion and then
querying the database:
public tblOpportunity GetOpportunityByCode(
string clientCode, string opportunityCode)
{
tblOpportunity opportunity = null;
var convertedOpportunityCode = Convert.ToInt32(opportunityCode);
ConnectionHandler.Invoke<EntityConnection>((connection) =>
{
var context = new xxEntities();
opportunity = context.tblOpportunities
.FirstOrDefault(o =>
o.ClientCode == clientCode &&
o.OpportunityCode ==
convertedOpportunityCode);
});
return opportunity;
}
|
| Store data in LINQ to two tables; Join LINQ |
SimpleMembership expects a single "UserProfile" per user, which it stores
in the table you specify in your call to:
WebSecurity.InitializeDatabaseConnection("DefaultConnection",
"UserProfile", "UserId", "UserName", autoCreateTables: true);
In the default code that table is called "UserProfile", which matches up
with UserProfile in AccountModels.cs. See the detail section 4 of my answer
to this question for more information about UserProfile.
I should also note that if it wasn't for your requirement "each user can
have multiple profiles" then you normally just add the properties such as
Email, FirstName, LastName, Country to the UserProfile class. I also move
that class out of UsersContext into my own context, delete UsersContext and
update the references from UsersContext to my own con
|
