I was given a SQL statement from my DBA to use within my application. I'm not sure how to convert it to code that LLBL can use. Here is my SQL statement.

(reformatted -- daelmo)

SELECT *
FROM CrewEmpInfo C 
WHERE 0 = 
    (
        SELECT Count(*) 
        FROM CrewAssign A 
        WHERE
            C.NTAN8 = A.NTAN8 
            AND( (StartDate BETWEEN A.StartDate ANDA.EndDate 
                OR EndDate BETWEEN A.StartDate and A.EndDate 
                OR (StartDate < A.StartDate ANDEndDate > A.EndDate)
                  )
                ) 
            AND BusinessUnit = C.BusinessUnit 
    )
ORDER BY SeniorityDate

I tried to write the statement but it seems that Linq doesn't like the between keyword.
Here is what I tried:

(reformatted -- daelmo)

Dim col =   
    From e In metaData.CrewEmployeeInfo 
    Where 0 = ( From a In metaData.CrewAssignment 
            Where e.Ntan8 = a.Ntan8 
            And (   (startDate >= a.StartDate And startDate <= a.EstimatedEndDate) 
                Or (endDate >= a.StartDate And endDate <= a.EstimatedEndDate) 
                Or (startDate < a.StartDate And endDate > a.EstimatedEndDate)
                ) 
            Order By e.SeniorityDate  And BusinessUnit = C.BusinessUnit
           ) 
    Select count(*) 
    Select e

Is there another way of doing this?

PS... I'm using SelfServicing.

I reformatted your queries in order to understand them better. BTW, your queries are missing closed parenthesis, please check that.

Linq2LLBL is not a 1-to-1 mapping from SQL to Linq code. Not even Linq is that. You should review your syntax in order to understand what is wrong. See the docs for more information. For instance 'between' is not a valid construct on Linq, so you cannot use it, but you can compare with >=, <=.

My advise is this: start with a very simple query that works, and then add parts incrementally. For your scalar query, this post could be helpful, an it has code snippets in LLBLGen API and Linq2LLBL: http://www.llblgening.com/archive/2009/09/llblgen-pro-expressions-and-scalar-queries/

NESLAutomation wrote:

I was given a SQL statement from my DBA to use within my application. I'm not sure how to convert it to code that LLBL can use. Here is my SQL statement.

Is there another way of doing this?

Another approach might be to create a view in the database for this query and then map it as a typed view an entity.