Hello,

I'm a new LLBLGen user and I'm struggling with how to use the generated code in this instance. I have a table of applicants, which joins to a key/value table of data. Currently I have a stored procedure that joins on the key/value table multiple times to return a single result set with 7 fields. How can I do this in LLBLGen?

Here is the existing sp:

SELECT
    a.applicantID,
    a.applicantStatusID As applicantStatusID,
    a.applicantPrintDate As applicantPrintDate,
    affd1.formElementData AS applicantFirstName,
    affd2.formElementData AS applicantLastName,
    affd3.formElementData AS brokerID,
    affd4.formElementData AS dateCompleted
FROM
    applicants a WITH (NOLOCK)
JOIN
    applicantFormFieldData affd1 WITH (NOLOCK)
ON
    a.applicantID = affd1.applicantID AND
    affd1.formFieldTypeID = 'applicantFirstName'
JOIN
    applicantFormFieldData affd2 WITH (NOLOCK)
ON
    a.applicantID = affd2.applicantID AND
    affd2.formFieldTypeID = 'applicantLastName'
JOIN
    applicantFormFieldData affd3 WITH (NOLOCK)
ON
    a.applicantID = affd3.applicantID AND
    affd3.formFieldTypeID = 'brokerID'
JOIN
    applicantFormFieldData affd4 WITH (NOLOCK)
ON
    a.applicantID = affd4.applicantID AND
    affd4.formFieldTypeID = 'currentDate'
JOIN
    applicantFormFieldData affd5 WITH (NOLOCK)
ON
    a.applicantID = affd5.applicantID AND
    affd5.formFieldTypeID = 'applicantState'
WHERE   
    a.applicantStatusID = COALESCE(@Status, a.applicantStatusID) AND
    CAST(affd4.formElementData AS smalldatetime) BETWEEN @dateFrom AND @dateTo

    AND CONVERT(datetime, affd4.formElementData) > '2004.12.15'
ORDER BY
    CAST(affd4.formElementData AS smalldatetime) DESC

Thanks, Tom

I'll get back to you on this first thing tomorrow morning. You can solve this with a dynamic list. Please check the dynamic list documentation in the TypedList/view documentation in 'Using the generated code / SelfServicing". In there you'll find an example of a dynamic list build by joining 2 times the same entity. Tomorrow I'll try to write some code for you which will mimic the joins in your query using LLBLGen Pro code.

I'm not sure this will work, as your query does converts and casts, which are not supported by the DQEs, but if I'm not mistaken, SqlServer will try to cast implicitly first, so it might be this will work.

The code below is perhaps a bit verbose, but a lot is copy paste or intellisense's work so you won't have to type a lot. I've replaced parameters and some hardcoded values with _<name> elements, you have to fill these in yourself. I'm not sure if the 5th join of ApplicantFormFieldData is really necessary, but just in case, it's there:

ResultsetFields fields = new ResultsetFields(7);
fields.DefineField(ApplicantFieldIndex.ApplicantId, 0, "ApplicantId");
fields.DefineField(ApplicantFieldIndex.ApplicantStatusId, 1, "ApplicantStatusID");
fields.DefineField(ApplicantFieldIndex.ApplicantPrintDate, 2, "ApplicantPrintDate");
fields.DefineField(ApplicantFormFieldDataFieldIndex.FormElementData, 3, "ApplicantFirstName", "Affd1");
fields.DefineField(ApplicantFormFieldDataFieldIndex.FormElementData, 4, "ApplicantLastName", "Affd2");
fields.DefineField(ApplicantFormFieldDataFieldIndex.FormElementData, 5, "BrokerID", "Affd3");
fields.DefineField(ApplicantFormFieldDataFieldIndex.FormElementData, 6, "DateCompleted", "Affd4");

IPredicateExpression affd1Filter = new PredicateExpression(
    PredicateFactory.CompareValue(
        ApplicantFormFieldDataFIeldIndex.FormElementData, ComparisonOperator.Equal, "applicantFirstName", "Affd1");
IPredicateExpression affd2Filter = new PredicateExpression(
    PredicateFactory.CompareValue(
        ApplicantFormFieldDataFIeldIndex.FormElementData, ComparisonOperator.Equal, "applicantLastName", "Affd2");
IPredicateExpression affd3Filter = new PredicateExpression(
    PredicateFactory.CompareValue(
        ApplicantFormFieldDataFIeldIndex.FormElementData, ComparisonOperator.Equal, "brokerID", "Affd3");
IPredicateExpression affd4Filter = new PredicateExpression(
    PredicateFactory.CompareValue(
        ApplicantFormFieldDataFIeldIndex.FormElementData, ComparisonOperator.Equal, "currentDate", "Affd4");
IPredicateExpression affd5Filter = new PredicateExpression(
    PredicateFactory.CompareValue(
        ApplicantFormFieldDataFIeldIndex.FormElementData, ComparisonOperator.Equal, "applicantState", "Affd5");
IRelationcollection relations = new RelationCollection();
relations.Add(ApplicantEntity.Relations.ApplicantFormFieldDataUsingApplicantId, "Affd1").CustomFilter = affd1Filter;
relations.Add(ApplicantEntity.Relations.ApplicantFormFieldDataUsingApplicantId, "Affd2").CustomFilter = affd2Filter;
relations.Add(ApplicantEntity.Relations.ApplicantFormFieldDataUsingApplicantId, "Affd3").CustomFilter = affd3Filter;
relations.Add(ApplicantEntity.Relations.ApplicantFormFieldDataUsingApplicantId, "Affd4").CustomFilter = affd4Filter;
relations.Add(ApplicantEntity.Relations.ApplicantFormFieldDataUsingApplicantId, "Affd5").CustomFilter = affd5Filter;

IPredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(ApplicantFieldIndex.ApplicantStatusId, ComparisonOperator.Equal, _status);
filter.AddWithAnd(PredicateFactory.Between(
    ApplicantFormFieldDataFIeldIndex.FormElementData, _dateFrom, _dateTo, "Affd4"));
filter.AddWithAnd(PredicateFactory.CompareValue(
    ApplicantFormFieldDataFIeldIndex.FormElementData, ComparisonOperator.GreaterThan, _date2004125, "Affd4"));
SortExpression sorter = new SortExpression(
    new SortClause(fields["dateCompleted"], SortOperator.Ascending));
    
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, sorter, filter, relations, true, null, null, 0, 0);

I couldn't help but notice the "NOLOCK" hints... I don't think these are supported today in LLBLGen?

Frans, there are times when these might be very handy, are you planning on adding them at any point?

Marcus

Well, they were on the plate a couple of times, however there is no real way to implement them properly, i.e.: pass on hints what to do, without awkward code for the user. NOLOCK is sqlserver specific, (and also the smaller engines use it, but I don't know if they have a NOLOCK hint), as Oracle and Db2 for example use a mvcc mechanism and never read dirty data from a table with locks.

The philosophy behind NOLOCK is that you read dirty data which is locked out for you. I don't think this is a good thing. NOLOCK can solve some deadlocks but it's often better to rework the code to avoid the deadlocks altogether than to opt for the easy way out of NOLOCK hints.

Though the DQE design is suitable for hints like this. I'll re-consider it in the near future.

Otis wrote:

The philosophy behind NOLOCK is that you read dirty data which is locked out for you. I don't think this is a good thing. NOLOCK can solve some deadlocks but it's often better to rework the code to avoid the deadlocks altogether than to opt for the easy way out of NOLOCK hints.

Yes I completely agree, I think they serve as useful hacks when the alternative code changes are prohibitive...