Hi i want to define a custom field at runtime on the basis of some condition such as if Field1 is null it should select field to into that field. If doing this is possible in self servicing kindly tell me how do i implement this??

You'd better use a DynamicList.

Otherwise if both fields are mapped into the corresponding Entity, then you may do this logic in code.

Hi there I need to implement following scenerio how do I implement this in self service with entities and collections

Select clientid, firstname, lastname, email,priority =
case when FirstName like '%test%' Then 1 
when LastName like '%test%' then 2
when Email like '%test%' Then 3 END
 from clients
where
(FirstName LIKE '%test%' OR 
LastName LIKE '%test%' OR
Email LIKE '%test%'
) 
Order by priority

In this case writting a custom property in your entity (in a partial class, or at USER_CODE_REGION) would do the work:

public int Priority
{
     get
     {
          if (Field1.Contains("test")
          {
               return 1;
          }
          // ...
     }
}

If you like the expression to be executed as an extra field in the collection fetch, then you have to extend the entity factory. Please take a look at the Setp 6 of this post: http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

Hi actually need a custom field priority on the basis of which i can sort my records, how do I implement this I am not getting from the link that you gave please elaborate

Just follow the 'Step 6 ' instructions. Here is another example: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=10287 In your case the expression you will add should be a DBFunctionCall that issues a CASE statement. Some example: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=13737&StartAtMessage=0&#76610

This approach has one caveat: If you extend the EntityFactory this way, you can't use that entity for saves, so just for read-only. Anyway if you want to do it, give it a try and let us know if you need further help on this. I recommend the first approach (do it in-memory). Sorting the in-memory collection with linq2Objects after it's fetched is not hard.

I can't use inmemory sort as the data is fetched on the basis of paging, therefore it wil sort only the data of 1 page only

could you explain using case with expression via dbfunctioncall

could you explain using case with expression via dbfunctioncall

Please check the link David posted earlier.

IMHO, it's easier to fetch this into a DynamicList (dataTable), as you won't be able to save the entities if you extend them with the extra field. So using a DynamicList, you can then define a field with an expression (DBFunctioCall), as in the code example pointed out by David.

I tried out following

 string pattern = string.Format("%{0}%", oSearchCriteriaDto.KeyWord);
                EntityField oEntityField = new EntityField("Pattern", new DbFunctionCall("case when {0} like '{1}' Then 1 when '{2}' like '{3}' then 2 when {4} like '{5}' Then 3 Else 4 END",
                                                                       new object[] { ClientFields.FirstName, pattern, ClientFields.LastName, pattern, ClientFields.Email, pattern }), typeof(int));
                oSortExpression.Add(oEntityField | SortOperator.Ascending);
                ResultsetFields oResultsetFields = new ResultsetFields(2);
                EntityField oEntityFieldClientId = new EntityField(ClientFieldIndex.ClientId.ToString(), "DerivedClientId", typeof(int));
                oResultsetFields.DefineField(oEntityFieldClientId, 0);
                oResultsetFields.DefineField(oEntityField, 1);
                DerivedTableDefinition oDerivedTableDefinition = new DerivedTableDefinition(oResultsetFields, "PriorityList");
                IDynamicRelation oDynamicRelation = new DynamicRelation(oDerivedTableDefinition, JoinHint.Inner, EntityType.ClientEntity, string.Empty, oEntityFieldClientId == ClientFields.ClientId);
                relations.Add(oDynamicRelation);

But I am getting following error :

An exception was caught during the execution of a retrieval query: The multi-part identifier "DerivedClientId.ClientId" could not be bound. The multi-part identifier "Old.dbo.Clients.FirstName" could not be bound. The multi-part identifier "Old.dbo.Clients.Email" could not be bound. The multi-part identifier "DerivedClientId.ClientId" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

EntityField oEntityFieldClientId = new EntityField(ClientFieldIndex.ClientId.ToString(), "DerivedClientId", typeof(int));

"DerivedClientId" here serves as the ObjectALias, not as the FieldAlias. So it contradicts with the "PriorityList" alias used later.

You should use the same object alias, in the field and in the Derivedtable definition.

Hi there I used the same alias on both of the place:

string pattern = string.Format("%{0}%", oSearchCriteriaDto.KeyWord);
                EntityField oEntityField = new EntityField("Pattern", new DbFunctionCall("case when {0} like '{1}' Then 1 when {2} like '{3}' then 2 when {4} like '{5}' Then 3 Else 4 END",
                                                                     new object[] { ClientFields.FirstName, pattern, ClientFields.LastName, pattern, ClientFields.Email, pattern }), typeof(int));
                oSortExpression.Add(oEntityField | SortOperator.Ascending);
                ResultsetFields oResultsetFields = new ResultsetFields(2);
                EntityField oEntityFieldClientId = new EntityField(ClientFieldIndex.ClientId.ToString(), "PriorityList", typeof(int));
                oResultsetFields.DefineField(oEntityFieldClientId, 0);
                oResultsetFields.DefineField(oEntityField, 1);
                DerivedTableDefinition oDerivedTableDefinition = new DerivedTableDefinition(oResultsetFields, "PriorityList");
                IDynamicRelation oDynamicRelation = new DynamicRelation(oDerivedTableDefinition, JoinHint.Inner, EntityType.ClientEntity, string.Empty, oEntityFieldClientId == ClientFields.ClientId);
                relations.Add(oDynamicRelation);

But still I am getting this error

An exception was caught during the execution of a retrieval query: The multi-part identifier "LPA_P1.ClientId" could not be bound. The multi-part identifier "Old.dbo.Clients.FirstName" could not be bound. The multi-part identifier "Old.dbo.Clients.Email" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Kindly advice

I guess my previous remark was not accurate, sorry about that. Please try the following code:

EntityField oEntityField = new EntityField("Pattern", new DbFunctionCall("case when {0} like '{1}' Then 1 when {2} like '{3}' then 2 when {4} like '{5}' Then 3 Else 4 END", new object[] { ClientFields.FirstName, pattern, ClientFields.LastName, pattern, ClientFields.Email, pattern }), typeof(int));

ResultsetFields oResultsetFields = new ResultsetFields(2);

oResultsetFields.DefineField(ClientFields.ClientId, 0);
oResultsetFields.DefineField(oEntityField, 1);

DerivedTableDefinition oDerivedTableDefinition = new DerivedTableDefinition(oResultsetFields, "PriorityList");

IDynamicRelation oDynamicRelation = new DynamicRelation(oDerivedTableDefinition, JoinHint.Inner, EntityType.ClientEntity, "C", 
(new EntityField(ClientFieldIndex.ClientId.ToString(), "PriorityList", typeof(int)) == ClientFields.ClientId.SetObjectAlias("C")));

I am still getting following error:

Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

Help me out I really need to sort the records on the basis of that pattern field ( generated from cases) !

I also tried sorting as follows

string pattern = string.Format("%{0}%", oSearchCriteriaDto.KeyWord);
                EntityField oEntityField = new EntityField("Pattern", new DbFunctionCall("case when {0} like {1} Then 1 when {2} like {3} then 2 when {4} like {5}' Then 3 Else 4 END",
                                                                       new object[] { ClientFields.FirstName, pattern, ClientFields.LastName, pattern, ClientFields.Email, pattern }), typeof(int));
                oSortExpression.Add(oEntityField | SortOperator.Ascending);

But showed invalid column name Pattern

I am still getting following error:

Quote: Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

Help me out I really need to sort the records on the basis of that pattern field ( generated from cases)

Did you add anything to my code, or you just tested it as it is?

According to the code example in the docs, the dynamicRelation should be used ass follows:

RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(oDynamicRelation);
filter.SelectListAlias = "C";

It might be that you have missed the SelectListAlias

I used the code as it is, later from the documentation I also tried adding the select list alias but the error was never resolved.

Hi Chirag,

Sorry for the frustration, but I think there is a little bit of communitacion problems

Ok, first of all, I don't see any obvious error in Walaa's code. Anyway, I don't know why you started using DerivedTable and DynamicRelations as from your first posts, the problem seems quite simple: You want your clients, ordered by a CASE...END expression. So let's start from that.

There are two ways (among others, but let's keep this simple) you can achieve this:

A. Use DynamicList (note that this is not DynamicRelation which is rarely used, just use this for advance joins). Maybe a Northwind example would help here. Say I want to fetch Employees that ultimately will generate a query like this:

SELECT 
    EmployeeID, 
    FirstName, 
    LastName, 
    CASE 
        WHEN FirstName LIKE '%d%' THEN 1 
        WHEN LastName LIKE '%d%' THEN 2 
        ELSE 3 
    END AS Priority
FROM Employees
ORDER BY Priority

The code that achieve above would look like:

// define my desired fields
var fields = new ResultsetFields(4);
fields.DefineField(EmployeeFields.EmployeeId, 0);
fields.DefineField(EmployeeFields.FirstName, 1);
fields.DefineField(EmployeeFields.LastName, 2);
        
// this is the epxression field
var pattern = "%d%";
var expr = new DbFunctionCall("CASE WHEN {0} LIKE {1} THEN {2} WHEN  {3} LIKE {4} THEN {5} ELSE {6} END",
        new object[] { EmployeeFields.FirstName, pattern, 1, EmployeeFields.LastName, pattern, 2, 3 });

var specialField = new EntityField("Priority", expr, typeof(int));
fields.DefineField(specialField, 3);

// I want to sort on that special field
var sortExpr = new SortExpression(specialField | SortOperator.Ascending);

// fetch
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, sortExpr, null, null, true, null);

// tests
Assert.AreEqual(2, dynamicList.Rows[0].ItemArray[0]);
Assert.AreEqual(8, dynamicList.Rows[dynamicList.Rows.Count - 1].ItemArray[0]); 

B. Fetch EmployeeCollection and sort on that CASE expression. This wont fetch the expression as part of the fields (that Priority filed), but you can sort on that. This is the code that would generate above sql:

var employees = new EmployeeCollection();
                        
// sort on an special field
var pattern = "%d%";
var expr = new DbFunctionCall("CASE WHEN {0} LIKE {1} THEN {2} WHEN  {3} LIKE {4} THEN {5} ELSE {6} END",
        new object[] { EmployeeFields.FirstName, pattern, 1, EmployeeFields.LastName, pattern, 2, 3 });

var specialField = new EntityField("Priority", expr, typeof(int));
var specialSortClause = new SortClause(specialField, SortOperator.Ascending);
specialSortClause.EmitAliasForExpressionAggregateField = false;
var sortExpr = new SortExpression(specialSortClause);

// fetch
employees.GetMulti(null, 0, sortExpr);
            
// tests
Assert.AreEqual(2, employees.First().EmployeeId);
Assert.AreEqual(8, employees.Last().EmployeeId);

... the trick is made by the specialSortClause.EmitAliasForExpressionAggregateField = false; line.

I think that those two approaches are enough for you. If you like the 2nd approach but you want to have a "Priority" property on your entity you can implement a custom property as I explained in my first post.

So, now you just have to transport this concept to your code. Let us know if you need further help on this.

Hi there Thanks for the code, it seems to be working . I just need to match results so could you tell me, What's the name space for Asserts??

http://msdn.microsoft.com/en-us/library/microsoft.visualstudio.testtools.unittesting.assert.aspx

http://msdn.microsoft.com/en-us/library/kssw4w7z.aspx