Hello Every body Since i'am new to LLBLGen i hope you can help me out

I want to transform the MSSQL script below to llblgen code. any help will be very much appreciated. thanks in adavance

Here is my MSSQL script

*SELECT v.,r.ApprovedDate FROM Visitation v left outer join Recommendation r on (r.VisitationId=v.VisitationId) where v.VisitationId not in ( select VisititationId from VisitationComment union select VisitationId from FollowUpEntry union select VisitationId from Recommendation where ApprovedDate IS NOT NULL and ApprovedDate < dateadd(month,datediff(month,0,getdate())-12,0) union select VisitationId from VisitationEmployee union select VisitationId from VisitationToProblemDefinition )

and v.VisitationStatusId <> 3 **

kind Regards /lm

Hi bluefish, rewriting the SQL like this

SELECT v.*, 
    r.ApprovedDate 
FROM Visitation v
    LEFT OUTER JOIN Recommendation r ON r.VisitationId = v.VisitationId
WHERE 
    v.VisitationId NOT IN (SELECT VisititationId FROM VisitationComment)
    AND v.VisitationId NOT IN (SELECT VisititationId FROM FollowUpEntry)
    AND v.VisitationId NOT IN (SELECT VisititationId FROM Recommendation
        WHERE ApprovedDate IS NOT NULL AND ApprovedDate < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())-12, 0) )
    AND v.VisitationId NOT IN (SELECT VisititationId FROM VisitationEmployee)
    AND v.VisitationId NOT IN (SELECT VisititationId FROM VisitationToProblemDefinition)                            
    AND v.VisitationStatusId <> 3

you have (at least) two options:

A. Use DynamicLists:

// DEFINE FIELDS
// here "n" is the number of fields you want to fetch
EntityFields2 fields = new EntityFields2(n);
fields.DefineField(VisitationFields.VisitationId, 0);
fields.DefineField(VisitationFields.AnotherField, 1);
// ... so on (add the fields you want from Visitation 
fields.DefineField(RecommendationFields.CompanyName, 10);

// BUILD THE FILTER
// define the filters. you also have to include the relation to Recommendation.
IRelationPredicateBucket filter = new RelationPredicateBucket();
bucket.Relations.Add(VisitationsEntity.Relations.RecommendationEntityUsingRecommendationId);

filter.PredicateExpression.Add( new FieldCompareSetPredicate(
    VisitationFields.VisitationId, null, VisitationCommentFields.VisitationId, null, SetOperator.In, null, true));

filter.PredicateExpression.Add(new FieldCompareSetPredicate(
    VisitationFields.VisitationId, null, FollowUpEntryFields.VisitationId, null, SetOperator.In, null, true));

// recommendation sub-filter
DbFunctionCall dateAddFunc = new DbFunctionCall("DATEADD(MONTH, DATEDIFF(MONTH, 0, GATEDATE()-1))-12, 0)", new object[] { });

filter.PredicateExpression.Add(new FieldCompareSetPredicate(
    VisitationFields.VisitationId, null, RecommendationFields.VisitationId, null, SetOperator.In,
        RecommendationFields.ApprovedDate != System.DBNull.Value
        && RecommendationFields.ApprovedDate < RecommendationFields.ApprovedDate.SetExpression(dateAddFunc), true));

filter.PredicateExpression.Add(new FieldCompareSetPredicate(
    VisitationFields.VisitationId, null, VisitationEmployeeFields.VisitationId, null, SetOperator.In, null, true));

filter.PredicateExpression.Add(new FieldCompareSetPredicate(
    VisitationFields.VisitationId, null, VisitationToProblemDefinitionFields.VisitationId, null, SetOperator.In, null, true));


filter.PredicateExpression.Add(VisitationFields.VisitationStatusId != 3);


// FETCH RESULTS
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, filter);
}

B. Fetching EntityCollection + PrefetchPath:

// DEFINE THE COLLECTION TO FETCH AND PREFETCHPATH
EntityCollection<VisitationEntity> visitations = new EntityCollection<VisitationEntity>(new VisitationEntityFactory());

IPrefetchPath2 path = new PrefetchPath2((int)EntityType.VsitationEntity);
path.Add(VisitationEntity.PrefetchPathRecommendations);

// BUILD THE FILTER
// same above filter ...

// FETCH RESULTS
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(visitations, filter);
}

Above is untested code and I assumed you are using LLBLGenPro v2.5, Adapter scenario and C#, next time please post these relevant information. Also, generally these kind of questions are posted at Generated Code Forum.

Hope helpful

Thanks very much

Yes i forget to mention that i'am using LLBLgen version 2.0 , self servicing and** mssql 2005**

Kind regards /Lm

Ok. For SelfServicing is likely similar. I'll close this thread for now. Feel free to reopen-it if you have any troubles.

Cheers.