Linq Entity with filter

data retrival

Many times we need to joins with filters in one entity or both of them. Here is the simple sample …

var tcIncentiveId = new Guid("31EE1300-AA49-E711-8100-000D3AD073D7");
 //context.Trace("trace " + tcIncentiveId);
 
 Entity tcincentive = service.Retrieve("aams_tcincentive", tcIncentiveId, new ColumnSet(true));

 //first entity - 
 QueryExpression queryFirstEntity = new QueryExpression("soft_tcincentive");
 
 //first entity columns to fetch
 queryFirstEntity.ColumnSet = new ColumnSet("soft_tcincentiveid");

 //first entity filters
 queryFirstEntity.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
 queryFirstEntity.Criteria.AddCondition("soft_tcincentiveid", ConditionOperator.Equal, tcIncentiveId);

 LinkEntity linkToEntity = new LinkEntity()
 {
 //first entity
 LinkFromEntityName = "soft_tcincentive",
 LinkFromAttributeName = "soft_incentiveid",

 //second entity
 LinkToEntityName = "soft_incentive",
 LinkToAttributeName = "soft_incentiveid",

 JoinOperator = JoinOperator.Inner,

 //second entity columns
 Columns = new ColumnSet("soft_incentivecode"),
 
 //second entity alias name
 EntityAlias = "soft_incentive"
 };

 //second entity filters
 linkToEntity.LinkCriteria.AddCondition("soft_incentivecode", ConditionOperator.In, "R6COMFFS", "R6RECOMFFS", "R6CMPFFS", "R6PRGFFS");
 
 //link second entity to fisrt entity
 queryFirstEntity.LinkEntities.Add(linkToEntity);

 //get result as collection
 EntityCollection results = service.RetrieveMultiple(queryFirstEntity);

 if (results.Entities.Count > 0)
 {
 //get first entity or itrate it ussing foreach 
 Entity result = results.Entities[0];

 //get values of second entity
 string columnValueFromSecondEntity = (result != null && result.Contains("soft_incentive.soft_incentivecode")) ? result.GetAliasedValue<string>("soft_incentive.soft_incentivecode") : null;
 //get values of first entity
 Guid? columnValueFromFirstEntity = (result != null && result.Contains("soft_tcincentiveid")) ? (Guid?)result.Attributes["soft_tcincentiveid"] : null;
 }

Thanks.

More retrieve queries   click this link to get more sample on data retrieval.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s