Databases
StackOverflow2013
Postsdbo
POTrouble converting a SQL Query to the equivalent LinqToEntities code?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble converting a SQL Query to the equivalent LinqToEntities code?
    text
    copied!<p><strong>The short:</strong> A simpler way of summing up this question would be, can you apply a conditon on a join in linq?</p> <p>I have the following sql query:</p> <pre><code>select cdm.cashID, cdm.DateTimeTillOpened, cdm.DateTimeTillClosed, o.OrderID, o.OrderDate from CashDrawsMonies cdm join Orders o on o.OrderDate &gt;= cdm.DateTimeTillOpened AND o.OrderDate &lt;= cdm.DateTimeTillClosed join Users u on o.UserID = u.UserID where u.UserID = 'C3763CC6-D1C5-4EF3-9B83-F7AB3BF8827A' group by cdm.cashID, cdm.DateTimeTillOpened, cdm.DateTimeTillClosed, o.OrderID, o.OrderDate order by o.OrderDate desc </code></pre> <p><em><strong>Alternative SQL@</em></strong></p> <pre><code>select * from CashDrawsMonies cdm where exists ( select * from Orders o join Users u on o.UserID = u.UserID where o.OrderDate &gt;= cdm.DateTimeTillOpened and o.OrderDate &lt;= cdm.DateTimeTillClosed and u.UserID = 'C3763CC6-D1C5-4EF3-9B83-F7AB3BF8827A' ) </code></pre> <p>I can convert most queries but on the join in Linq it always asks for an equal keyword and not something like >= or &lt;= which allows me in sql to put a condition on the join. This is what makes me scratch my head as to how do I convert it then?</p> <p>My linq-To-Entites model in code (c#) is set-up as:</p> <pre><code>Cache.Model.Orders Cache.Model.CashDrawMonies Cache.Model.Users </code></pre> <p>Appreciate the help.</p> <p><em><strong>Attempt 1:</em></strong></p> <pre><code> var results = from o in Cache.Model.Orders from c in Cache.Model.CashDrawMoneys join u in Cache.Model.Users on o.UserID equals u.UserID where c.DateTimeTillOpened &gt;= o.OrderDate &amp;&amp; c.DateTimeTillClosed &lt;= o.OrderDate select c; </code></pre> <p><em><strong>Attempt 2:</em></strong></p> <pre><code> var results = from c in Cache.Model.CashDrawMoneys from o in Cache.Model.Orders where c.DateTimeTillOpened &gt;= o.OrderDate &amp;&amp; c.DateTimeTillClosed &lt;= o.OrderDate group c by new { c.cashID, c.DateTimeTillOpened, c.DateTimeTillClosed, o.OrderID, o.OrderDate, o.UserID } into temp from t in temp join u in Cache.Model.Users on t.UserID equals u.UserID where t.UserID == selectedUser.UserID select t; </code></pre> <p>Returns no results .... : S</p> <p><em><strong>UPDATE:</em></strong></p> <p>I re wrote my sql, the second peiece of sql does exactly what I want know. Just need someone to convert it for me somehow???</p> <p>SQL Version 2 Converted Using Linqer:</p> <pre><code>from cdm in db.CashDrawMoneys where (from o in db.Orders join u in db.Users on o.UserID equals u.UserID where o.OrderDate &gt;= cdm.DateTimeTillOpened &amp;&amp; o.OrderDate &lt;= cdm.DateTimeTillClosed &amp;&amp; u.UserID == new Guid("C3763CC6-D1C5-4EF3-9B83-F7AB3BF8827A") select new { o, u }).FirstOrDefault() != null select new { cdm.UserID, cdm.DateTimeTillClosed, cdm.DateTimeTillOpened, cdm.LooseChange, cdm.Fivers, cdm.Tens, cdm.Twenties, cdm.Fifties, cdm.IsOpen, cdm.IsClosed, cdm.ClosingValue, cdm.OpeningValue, cdm.cashID } </code></pre> <p>Compiles nbut produces the following error:</p> <p>"The argument to DbIsNullExpression must refer to a primitive or reference type."</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload