CODEWITHSUNDEEP
sqlvb.netlinq
BAD_SEED
BAD_SEED

Reputation: 5056

Linq To SQL: left join and vb.NET

I have this SQL query: 

SELECT TOP 1 r.* FROM dbo.Request r
  LEFT JOIN dbo.Process p
  ON r.IdReq = p.IdReq
WHERE IdProcess Is NULL
ORDER BY r.ReqDate ASC

I want write it in a vb.net procedure, using Linq To SQL syntax:

Private Function getOldestRequest() As Request
    Dim dc As ProcessDataContext = new ProcessDataContext(ConfigurationManager.ConnectionStrings("...").ConnectionString);

    Dim pr = From r In dc.Request
             From p In dc.Process.Where(Function(v) v.IdReq = r.IdReq And v.idProcess Is Nothing)
             Select New {Request = r}

    Return pr
End Function

This is what I wrote, but I get error, on the Return line.

Upvotes: 0

Views: 557

Answers (2)

Bo TX
Bo TX

Reputation: 424

LINQ statements return IEnumerable objects, and your method is returning a single Request object. In your case looks like you only need to return the first result of the LINQ statement.

Something like:

    Dim pr = (From r In dc.Request
         From p In dc.Process.Where(Function(v) v.IdReq = r.IdReq And v.idProcess Is Nothing)
         Select New {Request = r}).FirstOfDefault
    Return pr

Upvotes: 1

Jens Kloster
Jens Kloster

Reputation: 11277

Change your Select to 

Select r

And your return to

Return pr.OrderBy(Function(v) v.RecDate).FirstOrDefault

Upvotes: 0

Related Questions