I have following SQL Query , due to some limitation because there are many more conditions in this query I have to convert this query to LINQ.

SELECT
    sh.BarCode
FROM
    Bars AS sh

    INNER JOIN BarDetail AS detail ON
        detail.BarCode = sh.BarCode
        AND
        detail.IsActive = 1

    INNER JOIN BarStatus AS st ON
        st.BarCode = sh.BarCode
        AND
        st.IsActive = 1

So I have done this so far at LINQ

var queryAble = _context.BarDetail
    .Include(x => x.Bar)
    .Include(x => x.Bar)
    .ThenInclude(y => y.BarStatus)
    .Where(x => x.IsActive == true)
    .AsQueryable();

I want to apply condition on barstatus as well; condition is barstatus with IsActive == true. I am unable to do it .

I don't want to do it as raw SQL with DbCommand, I'd like to do it entirely using only Linq-to-Entities.

How would I do this maybe this way but its not working

var queryAble = _context.BarDetail
    .Include(x => x.Bar)
    .Include(x => x.Bar)
    .ThenInclude(y => y.BarStatus)
    .Where(x => x.IsActive == true && x.Bar.BarStatus[SOMETHING HERE])
    .AsQueryable();

CodePudding user response：

This is direct translation of your SQL to LINQ. Note that Include intorduced not for building query but for loading related data.

var query =
    from bar in _context.Bar
    from detail in bar.Details
    where detail.IsActive && bar.IsActive && bar.BarStatus.IsActive
    select bar.BarCode;

CodePudding user response：

As I remarked in my comment, while your original SQL query works, it's best for JOIN clauses to use only key (or tuple) equality, while other predicates should be in the WHERE clause. Following that pattern shouldn't cause any changes to your runtime query execution plan, but I feel it's keeping with the relational-calculus that SQL is based on - and it also means you can instantly check if a JOIN is correct or not because you'll always be using only primary-key and foreign-key columns (which are presumably already indexed... right?).

So your query becomes:

SELECT
    b.BarCode
FROM
    Bars AS b
    INNER JOIN BarDetail AS d ON d.BarCode = b.BarCode
    INNER JOIN BarStatus AS s ON s.BarCode = b.BarCode
WHERE
    d.IsActive = 1
    AND
    s.IsActive = 1

...which is easier to translate into Linq-to-Entities:

Also:

• You don't need the .AsQueryable() call: all non-materialized queries created from DbContext's DbSet<T> will be IQueryable<T> already.
• As you have navigation-properties you don't need to do a manual Join.

IQueryable<String> q = _context.BarCode
//  .Include( b => b.BarDetail )
//  .Include( b => b.BarStatus )
    .Where( b =>
        b.BarDetail.IsActive == true
        &&
        b.BarStatus.IsActive == true
    )
    .Select( b =>  b.BarCode );

List<String> list = await q.ToListAsync( cancellationToken ).ConfigureAwait(false);

Update: Without b.BarDetail.IsActive == true

I can not directly add Include( b => b.BarStatus ) because BarStatus doesn't have direct relationship with BarCode, its linked with BarDetail. So first we go into BarDetail and then after that we go in BarStatus using BarDetail

You can still do a manual JOIN:

IQueryable<String> q = _context.BarCode
//  .Include( b => b.BarDetail )
//  .Include( b => b.BarStatus )
    .Join( _context.BarStatus, s => s.BarCode, b => b.BarCode, ( s, b ) 
 => new { BarStatus = s, BarCode = b, BarDetail = b.BarDetail } )
    .Where( t =>
        t.BarDetail.IsActive == true
        &&
        t.BarStatus.IsActive == true
    )
    .Select( b => b.BarCode );

List<String> list = await q.ToListAsync( cancellationToken ).ConfigureAwait(false);