How to Convert sql server query to linq in entity framework

Question

I am converting the sql server query to linq

 SELECT * FROM DataFlow where FlowDeleted = 0 and 
    DataFlow.FlowVersionNumber = 
    (
    SELECT MAX(FlowVersionNumber) from DataFlow i
    where FlowDeleted = 0 AND
    i.FlowCounter = DataFlow.FlowCounter
    group by FlowCounter
    )
    ORDER by 1

My Linq query Code is:

     public List<DataFlow> getdataflow() 
    var dflow = db.DataFlows.Where (
        d => d.FlowDeleted == false &&

        d.FlowVersionNumber =
        (
            db.DataFlows.Where(i => i.FlowDeleted == false && i.FlowCounter == d.FlowCounter).GroupBy(g => g.FlowCounter)
                .Select(s => s.Max(m => m.FlowVersionNumber))
        )
    )

.Select(s =>
    new DataFlow
    {
        FlowCounter = s.FlowCounter,
        FlowDescription = s.FlowDescription,
        FlowName = s.FlowName,
    }).OrderBy(o => o.FlowCounter);

    return dflow.ToList();
}

But it giving error like: Operator '&&' cannot be applied to operands of type 'bool' ... 'string'"

Please help me

Answer 1

You are using assignment operator instead of comparing... try ..d.FlowVersionNumber ==...

db.DataFlows.Where(d => 
    d.FlowDeleted == false &&
    d.FlowVersionNumber == db.DataFlows.Where(i => 
        i.FlowDeleted == false && 
        i.FlowCounter == d.FlowCounter).GroupBy(g => g.FlowCounter)
                                       .First(s => s.Max(m => m.FlowVersionNumber))
    )
)

I changed Select to First because you need to compare scalars. However, be sure that you WILL find an item, or you could get an exception.