Convert T-sql to Linq or Entityframework and not to use SqlQuery() for raw queries

Question

I have a table which is called Signer :

+--------------+----------+---------+--------+--------------+---------+-----------+
|     Name     |    User  |   Order | Signed | CompanyName  | Status  | InvoiceId |
+--------------+----------+---------+--------+--------------+---------+-----------+
|  Anders      |    aa    |       1 |      0 | OvnAnd2      |       0 |     26650 |
|  Peyman      |    pm    |       2 |      1 | OvnAnd2      |       1 |     26650 |
| Siw Ericsson |    se    |       3 |      0 | OvnAnd2      |       0 |     26650 |
|  test        |    test  |       4 |      0 | OvnAnd2      |       0 |     26650 |
|Siw Ericsson  |    se    |       1 |      0 | OvnAnd2      |       0 |     26652 |
|  test        |    test  |       2 |      1 | OvnAnd2      |       0 |     26652 |
|Siw Ericsson  |    se    |       1 |      0 | OvnAnd2      |       0 |     25365 |
+--------------+----------+---------+--------+--------------+---------+-----------+

Goal: As you can see I have 3 different InvoiceId's. For each InvoiceId, I would like to find a row with minimum order number that Status column's value is 0 and User column has to be se. ( It means, Show the current users related invoices which are ready to be signed based on his/her username, order, signed columns)

I came up with this T-SQL which works fine :

select * from Signer s1
where s1.User = 'se' and Order = (select min(Order) from Signer s2 where s2.InvoiceId = s1.InvoiceId and Signed = 0)

And the result:

+--------------+----------+---------+--------+--------------+---------+-----------+
|     Name     |    User  |   Order | Signed | CompanyName  | Status  | InvoiceId |
+--------------+----------+---------+--------+--------------+---------+-----------+
|Siw Ericsson  |    se    |       1 |      0 | OvnAnd2      |       0 |     26652 |
|Siw Ericsson  |    se    |       1 |      0 | OvnAnd2      |       0 |     25365 |
+--------------+----------+---------+--------+--------------+---------+-----------+

I would like to convert this query from T-Sql to Linq or EntityFramework :

This is my solution:

        var temp = from x in db.Signers
                    where x.User == Me.UserName &&
                          x.Signed == 0
                          group x by x.InvoiceId
                    into item
                    select new
                    {
                        item.Key,
                        item = item.Min(x => x.Order)
                    };

Which returns 3 rows which is wrong because Siw should see the related invoices that are ready to be signed by her. (It means the first row should not be in the list)

+--------------+----------+---------+--------+--------------+---------+-----------+
|     Name     |    User  |   Order | Signed | CompanyName  | Status  | InvoiceId |
+--------------+----------+---------+--------+--------------+---------+-----------+
|  Anders      |    aa    |       1 |      0 | OvnAnd2      |       0 |     26650 |
|Siw Ericsson  |    se    |       1 |      0 | OvnAnd2      |       0 |     26652 |
|Siw Ericsson  |    se    |       1 |      0 | OvnAnd2      |       0 |     25365 |
+--------------+----------+---------+--------+--------------+---------+-----------+

More info: - As you can see in the first table, we have a special logic that someone can sign invoices out of order and Peyman is one of them. - I don't want to use SqlQuery() method in Entityframework in order to execute t-sql queries.

I appreciate any help to find a solution for my goal. Best regards

Answer 1

I think this is one of those situations where "let" come in handy:

var result = from s in Signers
    let minToSign = Signers.Where(si => 
        si.InvoiceId == s.InvoiceId && si.Signed == 0
    ).Min(si => si.Order)
    where s.User == "se" && s.Order == minToSign
    select s;