If you are coding .NET projects, one of features I miss most compared to Java is a good Object Relational Mapping tool. Java programmers for years have been using tools such as Hibernate to help with persistence code, but for .NET the tools have been fairly lacking. Although there are products such as NHibernate, they are still not as mature as the Java versions. Commercial offerings are usually very expensive. Recently I tried LLBLGen and found it to be a great solution with good support and a good feature set. On top of that, the price makes it a no-brainer option for even an independent developer working on a budget.

When installing the product, copy your license file into the program’s main directory (by default “C:\Program Files\Solutions Design\LLBLGen Pro”). When you generate code, you will need to reference your generated projects ( and DBSpecific) and the SD.LLBLGen.Pro.ORMSupportClasses.NET library. Finally, in the DBSpecific Project, drag the DatabaseSpecific/App.Config to your application’s main project (or add the contents of that file if you already have an app.config). With all of these steps done, you can access all the features of the product.

Make sure you read the section on “concepts” as it makes clear the underlying theory behind the product, which is good to understand. I wish all documentation was this clear. I will extend this story as I dig deeper in to this wonderful product.

A couple of changes to my normal pattern of table naming was required: normally I distinguish tables and view with tblName and viewName style prefixes. This product strips prefixes, so the preferred solution is to give table names singular names, and view plural names. This becomes important because to "firehose" data, you will want to use the TypedView. As an example, I have PolicyStatus and PolicyStatuses as the table and view respectively.

One thing to watch for: if you save an entity and wish to continue using it, you must reload the entity after the save or the system will complain that your copy is "out of synchronization".

this.adapter.SaveEntity(m_data);
this.adapter.FetchEntity(m_data); //Reload after saveA

Combo box value loads (and other firehose cursors) are best implemented by using a stored procedure and then loading from the result set (sorting provided by the procedure):

DataTable surplusLinesStatuses = RetrievalProcedures.RpSurplusLinesStatuses();
this.PolicySurplusLinesStatus.LoadTableItems(surplusLinesStatuses, "Status", "SurplusLinesStatusID");

Note that the order in which the combo box properties are set is important (this is found in the LoadTableItems method of my derived combo box object type):

this.BeginUpdate();
this.ValueMember = valueMember;
this.DisplayMember = displayMember; 
this.DataSource = data; //Set the members *before* setting the source. 
this.EndUpdate();

Note that if the data source was set *first* this would crash. This appears to be a .NET issue as it makes no sense to worry about order in which these are assigned. It only crashes if one of the items (usually the value member) ends up with a name containing ID at the end.

Pulling a collection of data in is quite easy:

CompanyEntityFactory companyEntityFactory = new CompanyEntityFactory();
EntityCollection companies = new EntityCollection(companyEntityFactory); 
ISortExpression sorter = new SortExpression(
  SortClauseFactory.Create(CompanyFieldIndex.CompanyName, SortOperator.Ascending));
adapter.FetchEntityCollection(companies, null, 0, sorter);

The most complex part of this is the sort expression. We use factories to generate the objects that are relevant, and this takes some getting use to. Also, naming conventions are critical, each table is TablenameEntity and has TablenameFieldIndex and TablenameEntityFactor objects created. The use is illustrated above.

A more complex example, here using another object to collect up the rows based on a relationship and with three sort filters:

CompanyEntity companyEntity = new CompanyEntity(CompanyGUID);
adapter.FetchEntity(companyEntity);

ISortExpression sorter = new SortExpression(
  SortClauseFactory.Create(LocationFieldIndex.State, SortOperator.Ascending));
sorter.Add(
  SortClauseFactory.Create(LocationFieldIndex.City,  SortOperator.Ascending));
sorter.Add(
  SortClauseFactory.Create(LocationFieldIndex.Zip, SortOperator.Ascending));

EntityCollection locations = companyEntity.Location;
adapter.FetchEntityCollection(locations, companyEntity.GetRelationInfoLocation(), 0, sorter);

Note the companyEntity.GetRelationInfoLocation() that is passed: this is how we restrict the collection to *related* records. Passing null here pulls everything in the table (which is what you want sometimes, but not if you went to the trouble of pulling in a related record most times.

Another action you may wish to perform is a direct update of data without loading the entity. Here is an example, updating a field without loading it (this is the equivelent of an UPDATE Login SET PasswordHash = @NewPassword WHERE LoginGuid = @LoginGuid followed by the parameter sets, connection creation and execution).

LoginEntity loginEntity = new LoginEntity();
loginEntity.LoginGuid = m_loginEntity.LoginGuid; //Update the current user
loginEntity.IsNew = false; //We exist in the DB already.
loginEntity.passhwordHash = this.newPassword.Text;
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.SaveEntity(loginEntity);

When using "self servicing" mode files, this next point is moot. When using adapter to control data access more carefully (which is how I would recommend the product be used, despite the convenience of self servicing mode) letting the adapter know what related objects to bring along with is important to the performance of the program. In this example, we are pulling all of the policies that relate to an insured:

InsuredEntity insuredEntity = new InsuredEntity(InsuredGUID);
adapter.FetchEntity(insuredEntity);

SortExpression sorter = new SortExpression(
    SortClauseFactory.Create(PolicyProspectFieldIndex.EffectiveDate, SortOperator.Ascending));

EntityCollection policies = insuredEntity.PolicyProspect;
PrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.PolicyProspectEntity);
prefetchPath.Add(PolicyProspectEntity.PrefetchPathPolicyType);
adapter.FetchEntityCollection(policies, insuredEntity.GetRelationInfoPolicyProspect(), 0, sorter, prefetchPath);

foreach(PolicyProspectEntity policyProspectEntity in policies)
{
...
}

He we see an entity relationship being used (the insured's policies can be found by filtering on insuredEntity.GetRelationInfoPolicyProspect(). We are sorting these entries by a date field, again similar to prior examples. The novel piece of this code is the use of a prefetchPath, here constructed with the PolicyProspectEntity and then given the PolicyProspectEntity.PrefetchPolicyType as an related entity to load. Running the SQL Profiler while this runs shows the following queries executed:

SELECT 
FROM Insured  
WHERE ( Insured.InsuredGUID = @InsuredGuid1)

SELECT 
FROM PolicyProspect  
WHERE ( PolicyProspect.InsuredGUID = @InsuredGuid1) 
ORDER BY PolicyProspect.EffectiveDate ASC

SELECT 
FROM PolicyType  
WHERE ( PolicyType.PolicyTypeID IN (
	SELECT PolicyProspect.PolicyTypeID AS PolicyTypeId 
	FROM PolicyProspect  
	WHERE ( PolicyProspect.InsuredGUID = @InsuredGuid1)))

Note that this is potentially more efficient than the standard join query, where the same policy type information might be repeated over and over again in the result set. Here we see that only those policy types that are relevant are preloaded, and each will only be preloaded only once. Compare that to the equivalent SQL code:

SELECT 
FROM Insured  
WHERE ( Insured.InsuredGUID = @InsuredGuid1)

select *
from PolicyProspect 
inner join PolicyType 
  on PolicyProspect.PolicyTypeID = PolicyType.PolicyTypeID
where PolicyProspect.InsuredGUID = @InsuredGUID

The direct SQL only makes two round trips instead of three, but potentially repeats policy type information needlessly.

Some typical patterns:

using System;
using ORM;
using ORM.DatabaseSpecific;
using ORM.EntityClasses;
using ORM.FactoryClasses;
using ORM.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace PolicyTracker.BL
{
    internal static class AgentManager
    {
        internal static AgentEntity CreateAgent(Guid locationGuid)
        {
            AgentEntity entity = new AgentEntity();
            entity.AgentGuid = Guid.NewGuid();
            entity.LocationGuid = locationGuid;
            return entity;
        }
        
        internal static string GetCaption(AgentEntity agent)
        {
            return agent.AgentName;
        }

        internal static AgentEntity GetAgent(Guid agentGuid)
        {
            AgentEntity agent = new AgentEntity(agentGuid);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
                adapter.FetchEntity(agent);

            return agent;
        }

        internal static EntityCollection GetReassignTargets(AgentEntity sourceAgent)
        {
            LocationEntity sourceLocation = new LocationEntity(sourceAgent.LocationGuid); //Agent to load
            PrefetchPath2 path = new PrefetchPath2((int) EntityType.LocationEntity); //Get the location of the agent.
            path.Add(LocationEntity.PrefetchPathCompany).SubPath.Add(CompanyEntity.PrefetchPathLocation);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntity(sourceLocation, path);
            }

            return sourceLocation.Company.Location;
        }

        internal static void Reassign(AgentEntity sourceAgent, LocationEntity targetLocation)
        {
            sourceAgent.LocationGuid = targetLocation.LocationGuid;

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.SaveEntity(sourceAgent);
            }
        }

        internal static EntityCollection GetInsureds(Guid agentGuid)
        {
            AgentEntity agentEntity = new AgentEntity(agentGuid);
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntity(agentEntity);

            SortExpression sorter = new SortExpression(
                SortClauseFactory.Create(InsuredFieldIndex.Name, SortOperator.Ascending));

            EntityCollection insureds = agentEntity.Insured;
            adapter.FetchEntityCollection(insureds, agentEntity.GetRelationInfoInsured(), 0, sorter);

            return insureds;
        }

        internal static LocationEntity GetLocation(AgentEntity agent)
        {
            return LocationManager.GetLocation(agent.LocationGuid);            
        }

        internal static InsuredEntity CreateChildInsured(AgentEntity agent)
        {
            InsuredEntity newInsured = new InsuredEntity();
            newInsured.InsuredGuid = Guid.NewGuid();
            newInsured.AgentGuid = agent.AgentGuid;
            return newInsured;
        }
    }
}

An example of an update performed against the data store (no loading of objects):

        /// <summary>
        /// Moves the child Locations to the new parent Company
        /// 
        /// update Location 
        /// set CompanyGuid = @targetGuid
        /// where CompanyGuid = @sourceGuid
        /// </summary>
        /// <param name="sourceGuid">Company to remove children from.</param>
        /// <param name="targetGuid">Company to add children to.</param>
        internal static void MergeChildEntities(Guid sourceGuid, Guid targetGuid)
        {
            LocationEntity updateLocation = new LocationEntity();
            updateLocation.CompanyGuid = targetGuid;

            RelationPredicateBucket filter = 
                new RelationPredicateBucket(LocationFields.CompanyGuid == sourceGuid);
            
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.UpdateEntitiesDirectly(updateLocation, filter);
            }            
        }

A note about migration between environments.

LLBLGen writes code that includes an explicit reference to the database being referenced. This is because the ORM can talk to multiple databases at once. However, this also introduces the requirement to be able to rewrite the database references on the fly: if your production or test environments have databases with different names, the config files must be updated to rewrite the "source" names to the new names.

Adding

<configSections>
<section name="sqlServerCatalogNameOverwrites" type="System.Configuration.NameValueSectionHandler" />
</configSections>

<sqlServerCatalogNameOverwrites>
<add key="original_name" value="new_name" />
</sqlServerCatalogNameOverwrites>

to the .config file for the application will ensure the proper code is executed against the database. Simply changing your connection string will *not* have the desired effect unless the exact same names are used for the databases. There is also a schema rewriting section that works the same, just at the schema level.

Update: an example of a sub-query. Here we are looking for the current record. If the effective date is in the future or is an older record, it is excluded.

FieldCompareSetPredicate newFieldCompareSetPredicate = new FieldCompareSetPredicate(
	OnlineFeeTaxFields.EffectiveDate, //Defines the "outer" field.
	null, 
	OnlineFeeTaxFields.EffectiveDate.SetObjectAlias("ft"), //Defines the "inner" query field. SetObjectAlias gives the table an alias, SetFieldAlias does the same for a field.
	null,
	SetOperator.Equal, //We are looking for equality.
	(
		OnlineFeeTaxFields.State == OnlineFeeTaxFields.State.SetObjectAlias("ft") & 
		OnlineFeeTaxFields.TransactionTypeId == OnlineFeeTaxFields.TransactionTypeId.SetObjectAlias("ft") & 
		OnlineFeeTaxFields.TransactionCompanyCode == OnlineFeeTaxFields.TransactionCompanyCode.SetObjectAlias("ft") &
		(
			OnlineFeeTaxFields.SecOrgId.SetObjectAlias("ft") == 4 | OnlineFeeTaxFields.SecOrgId.SetObjectAlias("ft") == -1)
	), //Above is a fairly complex "predicate" filtering the subquery
	null, //No relationship map
	"", //No name from the relationship map
	1, //We want the "top" 1 record.
	new SortExpression(OnlineFeeTaxFields.EffectiveDate.SetObjectAlias("ft") | SortOperator.Descending)); //Sort descending so the largest is on top

Here we have an example of *filtering* on a related table. Prefetch and filter are not directly related: we can filter on tables we don't fetch and fetch related tables we didn't filter on. Prefetched tables are filtered based on the relationships established in the database, so it isn't frequently a requirement to filter prefetched tables.

        public static EntityCollection GetCarriers(Guid programId)
        {
            EntityCollection carriers = new EntityCollection();
            PrefetchPath2 path = new PrefetchPath2(EntityType.CarrierEntity);
            path.Add(CarrierEntity.PrefetchPathCarrierProgram);
            RelationPredicateBucket bucket = new RelationPredicateBucket(
                CarrierProgramFields.ProgramGuid == programId);
            bucket.Relations.Add(CarrierEntity.Relations.CarrierProgramEntityUsingCarrierGuid);

            SortExpression sorter = new SortExpression(CarrierFields.CarrierName | SortOperator.Ascending);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
                adapter.FetchEntityCollection(carriers, bucket, 0, sorter, path);

            return carriers;

        }