score:3
Accepted answer
var result = from a in accounts
where a.id != 2
select new { count = (from x in signs
from y in signs
where x.accountid == 2 &&
y.accountid == a.id &&
x.docid == y.docid
select 1).count(),
a };
note: you could probably change the subquery to a join on docid
but i've left as is, so you can see the similarity between the sql and the linq.
example with a join:
var result = from a in accounts
where a.id != 2
select new { count = (from x in signs
join y in signs on x.docid equals y.docid
where x.accountid == 2 &&
y.accountid == a.id
select 1).count(),
a };
Source: stackoverflow.com
Related Query
- Having problems with writing sql query on linq
- Writing a SQL query with LINQ
- Convert Sql query with Group by and Having clause to Linq To Sql query in C#
- I'm Having Trouble With a SQL Query in LINQ
- Trouble translating SQL query to LINQ with join and having count
- Having problems converting a complicated LINQ DataContext C# query to a SQL query
- SQL to C# LINQ query with multiple group by inner queries and having clauses
- Linq to SQL - Query with multiple joins, sum, grouping, having
- SQL query with Group By and Having Clause in LINQ structure
- Having problems with a Lambda LINQ Query
- How do I most elegantly express left join with aggregate SQL as LINQ query
- linq to sql query with multiple where parameters
- LINQ - writing a query with distinct and orderby
- Simple sql to Linq query with group by and aggregate functions
- Is there any way to create a LINQ query as a variable without having the data source (yet)?
- Linq to entities - SQL Query - Where list contains object with 2 properties (or more)
- Is there a JS library that supports writing linq to sql queries with nodejs?
- Optimize SQL generated by LINQ Query in Entity Framework 4.1 with one-to-many associations
- Writing a sub query in LINQ with Top X
- Populate Excel with data from LINQ to SQL query
- Determine the source DataContext for a Linq to Sql query
- Retrieve and print data from dynamic sql query with linq
- How to get SQL query into LINQ form in C# code
- Complex LINQ to SQL Query with dates
- LINQ to Sql Left Outer Join with Group By and Having Clause
- Linq to Sql query with multiple aggregations
- Convert sql query with multiple inner joins to LINQ
- Having problems with inner joins in LINQ
- Reuse Linq to SQL code with entityframework
- Identify source of linq to sql query
More Query from same tag
- LINQ - Association mapping - Null reference on InsertOnSubmit
- Linq Query for dataset in c#?
- EF LINQ include multiple and nested entities
- xmlns attribute in LINQ to XML query
- C#: Iterating through a data table: Rows, Select() or AsEnumerable()
- Get the number of distinct property values in List<T> using LINQ in C#
- Joining tables together via junction table in LINQ
- LINQ ToList() serialization error?
- LINQ: How to Transpose 1D Array into 2D Array By Row
- How to create LINQ QUERY dynamically?
- Query Parent's record where condition on a Grandchild
- How to use the IEqualityComparer
- Group or sort list/array by number of matching keywords
- N-way intersection of sorted enumerables
- Use SelectMany to extract value from list and pass to groups
- Using the result of one LINQ query as the parameter for the next
- Best way to combine 2 lists
- Where clause in Linq and compare strings
- Using eager loading with specification pattern
- left outer join after a inner join linq c#
- Do something for every hundredth element in an array
- Select data from different object in linq query
- How to write this SQL query in VB.NET LINQ
- Nested query with Join in LINQ expression
- Show subset of data in MVC3 view page
- Unexpected behaviour using OR operator in MongoDB linq query
- Convert Python List Comprehension to C#
- How to add data from a List<List<String>> to a list view
- Linq - Retrieve value from a linked linked table
- EF Core 3.1 - The LINQ expression could not be translated (left joins with group by)