The ObjectMapper .NET Project

Official blog of the AdFactum ObjectMapper .NET

Archive for January, 2007

Registered Trademark

Posted by Gerhard Stephan on 29th January 2007

After one failed attempt, I’m proud to say that the "AdFactum ObjectMapper .NET" has now be approved as a registered trademark of the Federal Republic of Germany. The certificate was send to me few days ago.

The one failed attempt was because I could not register "ObjectMapper .NET" as a standalone trademark. It was to global to register, because it would hinder competing companies to merchandise any object mapping tool.

But now it’s accomplished.

So have a nice week
Cheers

Gerhard

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 2.50 out of 5)
Loading...

Posted in Miscellaneous | No Comments »

How to build sql queries

Posted by Gerhard Stephan on 22nd January 2007

The ObjectMapper .NET offers you a wide range of possibilities in order to build powerful sql queries with a minimum effort. The ObjectMapper .NET uses conditions in order to build a SQL Where Clause. All database queries are based on the interface ICondition.

A Condition is implemented using the builder pattern. Which means that a condition can be composed of other conditions. The result of the condition builder is the sql statement (ConditionString property). Conditions can be concatinated with the Method Add and a ConditionOperator which tells the Mapper how to connect two conditions.

The interface ICondition is implemented by a wide range of sub classes and some top level categories. The top level categories are:

  • Query Lists
  • Simple Conditions
  • Join Conditions
  • Sub Queries
  • Specialities

Now let us look at the top level categories in detail.

Query Lists : ConditionList, Parenthesize

The implementation ConditionList is primarily a list of concatinated conditions. Typically this object will be used as the top level container for a query. E.g.

new ConditionList(

   new AndCondition(typeof(Employee), "Id", user.Id),

   new CollectionJoin(typeof(Employee), "Contacts", typeof(Contact)) );

The parenthesize class does almost the same. The only difference is, that the parenthesize class will set the result statement within brackets.

Simple Conditions: AndCondition, OrCondition

The most used conditions are the simple conditions. Here are the possible use cases. The examples for the OrCondition are equivalent.

public AndCondition(Type queriedObjectType, string field, object compareValue)

E.g. new AndCondition(typeof(Employee), "Id", user.Id);

or

public AndCondition(Type queriedObjectType, string field, QueryOperator comparer, object compareValue)

E.g. new AndCondition(typeof(Company), "CompanyState", QueryOperator.NotEqual, CompanyState.Deleted);

 

The first example compares always using the QueryOperator.Equal. But the ObjectMapper .NET offers many more query operators.

      /// <summary>

      /// Enumeration class to define the operators for the where clause

      /// </summary>

      public enum QueryOperator

      {

            Equals,

            NotEqual,

            Lesser,

            Greater,

            LesserEqual,

            GreaterEqual,

            Is,

            IsNot,

            In,

            Like,

            NotIn,

            NotLike,

            Like_NoCaseSensitive,

            NotLike_NoCaseSensitive

      }

 

 

Join Conditions: Join, CollectionJoin

Joins are also Conditions. That makes it easy to build joins and combine them with other where clause conditions. A distinction is drawn between "Single Joins" and "Collection Joins". What is the difference? A "Single Join" is always a join to an aggregated object like that in the following example.

      public class Preference : BaseEntity

      {

            private Classification classification;

            public Classification Classification

            {

                  get { return classification; }

                  set { classification = value; }

            }

 

With this knowledge you can build the following Join between Preference and Classification.
new Join(typeof(Preference), "Classification", typeof(Classification), "Id")

The content of field "Classification" must equal the content of the field "Id" of the Classification class. In addtion you could query a field of the class Classification.

A collection join is almost the same, except the fact that the ObjectMapper .NET has to use a link table to establish the join. Have a look at the following example.

    class Company_GenericList_Contacts : Company

    {

        private List<Contact> contacts = new List<Contact>(); 

        public List<Contact> Contacts

        {

            get { return contacts; }

            set { contacts = value; }

        }

    }

 

The join would look like that:
new CollectionJoin(typeof(Company_GenericList_Contacts), "Contacts", typeof(Contact))
 

If you want to use outer joins you have to tag the joined class with a [WeakReferenced] Attribute.

    [WeakReferenced]

    public class Contact : BaseEntity

    {

 

If you want further informations about using outer joins, have a look at the blog entry [WeakReferenced].

Sub Queries: InCondition, NotInCondition, SubSelect, Union

The ObjectMapper .NET masters also InConditions, SubSelects and Unions. An InCondition is almost the same like known from native SQL. You have to use InConditions all the time if you want to use distinct query targets within one query.

E.g. You want to select all activities where the creator or the assigned employee have a special last name. In that example you want to query the last name of an employee that is joined in two different ways. First joined by field "CreatedByEmployee" and second time joined by field "AssignedEmployee".

 

SubSelect employees = new SubSelect(typeof(Employee), "Id",

    new AndCondition(typeof(Employee), "LastName", QueryOperator.Like_NoCaseSensitive, EmployeeLastName));

                        

Parenthesize employeeClause = new Parenthesize(

      new InCondition(ConditionOperator.OR, typeof(Activity), "AssignedEmployee", employees),

      new InCondition(ConditionOperator.OR, typeof(Activity), "CreatedByEmployee", employees));

 

In this example we first create a sub selection that selects the last name of an employee with a like condition. After that we are using two connected InConditions that queries the activity object with two different properties.

Furthermore I want you to have a look at the following example where the Union Condition will be used. The following example searches all companies where the user is employee or the user is a team member of the company. 

new InCondition(typeof(Company), "Id",

new Union(

            new SubSelect(typeof(Company), "Id",

                  new CollectionJoin(typeof(Company), "Employees", typeof(Employee)),

                  new AndCondition(typeof(Employee), "Id", QueryOperator.Equals, user.Id)

                  ),

            new SubSelect(typeof(Company), "Id",

                  new CollectionJoin(typeof(Company), "Teams", typeof(Team)),

              new CollectionJoin(typeof(Team), "Members", typeof(Employee)),

                  new AndCondition(typeof(Employee), "Id", QueryOperator.Equals, user.Id))));

Typically you would query two SQLs in order to get the result list. Using the ObjectMapper .NET you can handle the query all within one SQL statement. The InCondition, combined with the SubSelect and the Union are the most powerful select features of the ObjectMapper .NET. There’re several possibilities to use it. I recommend to play with these statements and check the resulting SQL statement.

Specialities: TableReplacement, CollectionChildCondition, CollectionParentCondition

The specialities of the ObjectMapper .NET can help you to handle special use cases. The TableReplacement condition for example replaces a table name with a self defined sql phrase, e.g. the name of a view or a sub select.

return new ConditionList(

new TableReplacement(typeof(Company), “SELECT * FROM COMPANY WHERE COMPANY.COMPANYTYPE=1”),

new AndCondition(typeof(Company), "CompanyState", QueryOperator.NotEqual, CompanyState.Deleted)

 

Instead of selecting from table company the SQL will use the given Phrase to build the from clause. Using this example the resulting SQL would look like that:
SELECT * FROM (SELECT * FROM COMPANY WHERE COMPANY.COMPANYTYPE=1) Company WHERE Company.CompanyState = 5

That enables you to use views or sub queries for special behaviours.

The CollectionParentCondition and CollectionChildCondition can be used as a shortcut that combines a CollectionJoin and an AndCondition. I implemented these two Conditions because it covers common use case.

new CollectionParentCondition (typeof(Employee), "Contacts", typeof(Contact), user.Id);

 

equals

 

new ConditionList(

   new AndCondition(typeof(Employee), "Id", user.Id),

   new CollectionJoin(typeof(Employee), "Contacts", typeof(Contact)) );

 

 

 

new CollectionChildCondition (typeof(Employee), "Contacts", typeof(Contact), contact.Id);

 

equals

 

new ConditionList(

   new CollectionJoin(typeof(Employee), "Contacts", typeof(Contact)),

   new AndCondition(typeof(Contact), "Id", contact.Id),

);

 

That’s all in all the first introduction to the AdFactum ObjectMapper .NET Query API. If you have any questions I would be glad to get comments on this blog entry.

Wish you a stress-free development and always good mapped classes 🙂
– Gerhard

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Posted in HowTo | No Comments »

New Release – ObjectMapper .NET 1.45.1222.1

Posted by Gerhard Stephan on 22nd January 2007

Hi Guys,

the new release of the ObjectMapper .NET fixes primarily one big bug that occured when searching for empty Guids. Which leads the ObjectMapper .NET to a null reference exception. That bug has been fixed now.

Additionally I changed the sql tracing parameter from a plain string the the interface IDbCommand. That enables you to log parameter values as well as the plain sql string.

So have fun with the new version
Cheers

Gerhard

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Posted in Releases | No Comments »