Simple Example Subquery Linq

Simple Example Subquery Linq

Simple answer is use the "let" keyword and generate a sub-query that supports your conditional set for the main entity.

var usersEnrolledInCourses = from u in User_Users
                                 let ces = from ce in Course_Enrollments
                                           select ce.UserID
                                 where ces.Contains(u.UserID)
                             select u;   

This will create an exists block in TSQL similar to

SELECT [Extent1].*
   FROM dbo.User_Users AS Extent1
   WHERE EXISTS (SELECT 1 AS [C1]
                     FROM dbo.Course_Enrollements AS Extent2
                     WHERE (Extent2.UserID = Extent1.UserId))

It's close to what you've asked for and will generally create the same query plan on SQL Server.

Hope this helps!

How to do a subquery in LINQ?

Here's a subquery for you!

List<int> IdsToFind = new List<int>() {2, 3, 4};

db.Users
.Where(u => SqlMethods.Like(u.LastName, "%fra%"))
.Where(u =>
    db.CompanyRolesToUsers
    .Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId))
    .Select(crtu =>  crtu.UserId)
    .Contains(u.Id)
)

---

Regarding this portion of the question:

predicateAnd = predicateAnd.And(c => c.LastName.Contains(
                                TextBoxLastName.Text.Trim()));

I strongly recommend extracting the string from the textbox before authoring the query.

string searchString = TextBoxLastName.Text.Trim();
predicateAnd = predicateAnd.And(c => c.LastName.Contains( searchString));

You want to maintain good control over what gets sent to the database. In the original code, one possible reading is that an untrimmed string gets sent into the database for trimming - which is not good work for the database to be doing.

LINQ subquery IN

var innerquery = from x in context.DataDictVal
                 where x.TbiTableName == myTableNameVariable
                    && x.DdColumnName == "Status"
                    && x.DdbColumnValLangDsc1.StartsWith("ac")
                 select x.DdvColumnVal;

var query = from c in context.Client
            where innerquery.Contains(c.Etat)
            select c;

LINQ TO SQL Subquery

This should work for you:-

var result = (from x in
               ((from data in db.DATA orderby data.ID descending select data).Take(2))
                orderby x.ID
                select x).FirstOrDefault();

Subquery in Where Clause of LINQ statement

I think a simple join would do the job. It will filter out the 'cheques' that have no relative 'app':

  var _entitylist = 
    from cheque in context.postDatedCheques
    join app in context.applications on cheque.appSancAdvice.application equals app
    select cheque;

Edit:

Solutions using a .Contains(...) will be translated into a SQL IN statement. Which will be very inefficient. Linq join is translated into SQL INNER JOIN which is very efficient if your DB schema is well trimmed (FKs, index)

LINQ how to translate subquery in FROM clause

What may help is going through a couple sample linq queries and look at the results. For example, the following code will create an IEnumerable of val1's.

var subSelect1 = (from val1 in aTable 
    where  val1.foo = "bar" 
);

Note the above is equivalent to the below.

var subSelect1 = (from val1 in aTable 
    where  val1.foo = "bar" 
    select val1 /* this select statement is implied in the above */
);

Adding the select new {val1.foobar} after the where clause creates an IEnumerable of an anonymous type, with one property named foobar. This means that you'll only be able to join against the one property foobar.

var subSelect1 = (from val1 in aTable
         where  val1.foo == "bar"
         select new {val1.foobar}
    );

var mainSelect = (from f in subSelect1
         where f.foobar == "test")

By leaving out the select new, you'll have access to all the fields in val1.

var subSelect1 = (from val1 in aTable 
    where  val1.foo == "bar" 
);

var mainSelect = (from v in subSelect1
    where v.foobar == "test"
        and v.bar == "status"
)

How to use GroupBy() in subqueries with LINQ?

Error message says that you have to include Id column in projection. But you can't do that with GroupBy. So rewrite query into two steps (removed not needed includes):

var rawRecords = UnitOfWork.FreeBets
    .Where(f => f.FreeBetCards.Any(cards => cards.UserId == request.UserId))
    .Select(f => new 
    {
        FreeBetId = f.FreeBetId
        LineCategories = f.FreeBetCategories.Select(c => new { c.Id, c.LineCategoryID, c.Title })
            .ToList()
    })
    .AsEnumerable();

var records = rawRecords
    .Select(f => new FreeBetDTO
    {
        FreeBetId = f.FreeBetId
        LineCategories = f.LineCategories.GroupBy(g => new { g.LineCategoryID, g.Title })
            .Select(c =>
                new LineCategoryDTO
                {
                    LineCategoryID = c.Key.LineCategoryID,
                    Title = c.Key.Title
                })
    });

Similar query, but more optimal:

var query =
    from f in UnitOfWork.FreeBets
    from c in f.FreeBetCards
    where f.FreeBetCards.Any(cards => cards.UserId == request.UserId)
    select new { f.FreeBetId, c.LineCategoryID, c.Title };

query = query.Distinct();

var records = query.AsEnumerable()
    .GroupBy(f => f.FreeBetId)
    .Select(g => new FreeBetDTO
    {
        FreeBetId = g.Key
        LineCategories = g.Select(c =>
                new LineCategoryDTO
                {
                    LineCategoryID = c.LineCategoryID,
                    Title = c.Title
                })
            .AsEnumerable()
    });

SQL Server query with subquery to LINQ query

This won't be the same SQL but it should produce the same result in memory:

var ytdClosureRateData = _context.EvaluationHistoryTable
.Where(t => t.InsertDate.Value.Year == DateTime.Now.Year)
.GroupBy(m => new
{
    Month = m.InsertDate.Value.Month
})
.Select(g => new
{
    Month = g.Key.Month,
    ItemCount = g.Count(),
    ClosedCount = g.Where(t => t.TicketStatus == "CLOSED").Count()
}).OrderBy(a => a.MonthNumber)
.ToList()
.Select(x => new YtdTicketClosureRateModel
{
    MonthName = DateTimeFormatInfo.CurrentInfo.GetAbbreviatedMonthName(x.Month),
    MonthNumber = x.Month,
    ItemCount = x.ItemCount,
    ClosedCount = x.ClosedCount,
    ClosureRate = x.ClosedCount * 100D / x.ItemCount
})
.ToList();

Two techniques have been implemented here:

1. Use Fluent Query to specify the filter to apply for the ClosedCount set, you can combine Fluent and Query syntax to your hearts content, they each have pros and cons, in this instance it just simplifed the syntax to do it this way.

2. Focus the DB query on only bringing back the data that you need, the rest can be easily calculated in member after the initial DB execution. That is why there are 2 projections here, the first should be expressed purely in SQL, the rest is evaluated as Linq to Objects

The general assumption is that traffic over the wire and serialization are generally the bottle necks for simple queries like this, so we force Linq to Entities (or Linq to SQL) to produce the smallest payload that is practical and build the rest or the values and calculations in memory.

---

UPDATE:

Svyatoslav Danyliv makes a really good point in this answer

The logic can be simplified, from both an SQL and LINQ perspective by using a CASE expression on the TicketStatus to return 1 or 0 and then we can simply sum that column, which means you can avoid a nested query and can simply join on the results.

Writing a subquery using LINQ in C#

Although it doesn't answer your question directly, what you are trying to do is much better suited for an inner join:

SELECT *
FROM SectionDataTable S1
   INNER JOIN SectionDataTable S2 ON S1.CourseID = S2.CourseID
WHERE S2.SectionID = iSectionID

This then could be modeled very similarily using linq:

 var query = from s1 in SectionDataTable
     join s2 in SectionDataTable
     on s1.CourseID equals s2.CourseID
     where s2.SectionID == iSectionID
     select s1;

---

Related Topics