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 Articles
- Having problems with writing sql query on linq
- Having problems with a Lambda LINQ Query
- LINQ - writing a query with distinct and orderby
- Is there any way to create a LINQ query as a variable without having the data source (yet)?
- Writing a sub query in LINQ with Top X
- Having problems with inner joins in LINQ
- Linq sub query when using a repository pattern with EF code first
- Convert linq query with having
- Avoiding repeating code with Linq query + optional params
- how to write a Linq query with a EF code first Many to Many relationship
- 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
- writing efficient query to search a dataset index record with linq
- linq with having and sub query
- Having problems converting a complicated LINQ DataContext C# query to a SQL query
- Difficulty with LINQ Query writing custom sort logic
- Writing a linq Query to add elements with duplicate IDs together
- SQL to C# LINQ query with multiple group by inner queries and having clauses
- Writing left join in linq query with 5 table
- Linq to SQL - Query with multiple joins, sum, grouping, having
- Linq query with left join having multiple tables
- Group By Having and Count as LINQ query with multiply nested tables
- Help with writing a linq query
- C# LINQ Query with group by, having and where clause invalid
- Proper Linq Query for objects with many to many relation ship generated with code first entity framework
- SQL query with Group By and Having Clause in LINQ structure
- Having trouble grouping columns in Linq query with multiple joins
- Problems with a linq query
- Performance between check exists before add to list and distinct in linq
- EF Core, append to predicate builder in Any condition
- Can I use Linq to project a new typed datarow?
- iterating over columns in a list of tuples with Linq
- Issues in converting SQL to LINQ
- How to call function inside linq anonymous function
- XDocument xml parsed but fails to save attributes. Xml.Linq
- What parameters do I need to put into InsertOnSubmit(...) if one or more of my columns is an identity column?
- Search words in list of structs and get all words with postfix
- Using linQ to group list of object into new lists of objects
- Computed property with AutoMapper
- linq: counting items in subsets of queries
- How to check the existence of Attribute in XDocument using LINQ to XML in C#
- C#, Winforms & LINQ to SQL.. Datacontext lifecycle?
- Linq select group by where syntax
- Way to populate class object in single LINQ XML query?
- NullReferenceException in LINQ Query
- Flatten a collection based on an internal property
- Include Derived Models Related Class
- Using join on same table three times