How to easily get a single record from table

[MWM]

Can someone provide me with simple code (C# preferrably) which will utilize the auto-generated classes per the OA wizard to
 
1) retrieve a single record from a table (using pk such as "id"),
2) edit that record through the use of an object, and
3) persist the object back to the db table?

I've tried without success, something like:

string

 

queryString = "select * from Org as x ";

 

IObjectScope

 

scope = Database.Get("DatabaseConnection1").GetObjectScope();
IQuery oqlQuery = scope.GetOqlQuery(queryString);
IQueryResult result = oqlQuery.Execute();

Org oneOrg = new Org();

 

oneOrg = (

Org)result;

 

result.Dispose();

As you can see, I need some direction...

[Burl]

Try this 2 part article I found today, should help.

http://object-relational-mapping.blogspot.com/2007/03/basic-crud-operations-part-12.html

Burl

[MWM]

Burl,

This could not have been more helpful!  Thanks so much.

[Erik]

Look at this new article:

http://blogs.telerik.com/blogs/posts/11-03-21/a_simpler_objectkey.aspx

Espeassially at tyhe comment from Jan Blessenohl, thats works fine if you extend the partial class OA context with it.

public T GetById<T>(int key)
    {
        return Context.GetObjectByKey<T>(new ObjectKey(typeof(T).FullName,key));
    }

public T GetById<T>(long key)
    {
        T t;
        Context.TryGetObjectByKey<T>(new ObjectKey(typeof(T).FullName,key), out t);
        return t;
    }

VB:

Public Function GetSingle(Of T)(aint_PKvalue As System.Int32) As T
    Return Me.GetObjectByKey(Of T)(New ObjectKey(GetType(T).FullName, aint_PKvalue))
End Function

Public Function GetSingle(Of T)(alng_PKvalue As Long) As T
    Return Me.GetObjectByKey(Of T)(New ObjectKey(GetType(T).FullName, CType(alng_PKvalue, System.Int32)))
End Function

[Erik]

Back again...

The code works fine, with some additional overloads.

Now I have some tables where the PK is an int and some with a bigint. The function crashes with the bigints

how do i determine the type of the PK for an entity?
It should be something with object key I suppose, but can't get it to work.

Thanks,

Erik

[Patrice Boissonneault]

And how about table where primary key is more than one field?

[Erik]

Yes, good point, so I'll rephrase: How can we analyze the PK of a entity?

[Petko_I]

Hi all,

@Mark
We have an online documentation topic that discusses the various usage of the OpenAccess API. To work with OpenAccess you have two approaches at your disposal. The one you have tried some operations with is the classic OpenAccess scope API. We are currently concentrating our efforts in the direction of the visual designer approach which is the other alternative. The visual designer approach automates a lot of tasks that previously with the scope API were accomplished with several steps. You will find that the documentation is basically split into two sections - each elaborating on use-case scenarios with the respective approach.

@Erik and Patrice
The primary key of an entity can be fully analyzed with the help of an ObjectKey instance. The ObjectKey has an array of ObjectKeyMembers. Persistent types with composite identity have their array of ObjectKeyMembers with a size greater than one. Thus you can differentiate between single-field and multiple-field identity for a persistent type. You can further analyze each ObjectKeyMember and obtain the name of the property that participates in the identity (ObjectKeyMember.Key) and also the property value (ObjectKeyMember.Value). The type of the identity property can be obtained with the help of reflection in the following manner:

OrderDetail orderDetail = context.OrderDetails.Where(od => od.Quantity > 10).First();
ObjectKey objectKey = ObjectKey.Create(orderDetail);
var objectKeyValues = objectKey.ObjectKeyValues;
ObjectKeyMember firstCompositeKeyMember = objectKeyValues[0];
Type firstCompositeKeyMemberType = firstCompositeKeyMember.Value.GetType();

To sum up, the new ObjectKey API is designed to do just that -  non-trivial handling of the identity of persistent types. We hope the provided information is useful. Do not hesitate to contact us, If you have further doubts regarding the usage of the ObjectKey.

Kind regards,
Petko_I
the Telerik team

[Erik]

Thanks Petko_I for responding,

I see wat you are doing, but I ran into other issues yesterday as wel, also concerning identity.

I see your example, but how does that translate to the GetById function? In your example you know the entity object and pass it to the ObjectKey.Create function. But in the GetById (or vb GetSingle) function you do not have a queryable object, but just the type of the entity. When I use your code, it gives me the type of the parameter Key (regarding GetById function) and not the type of the identity. Also the name of the identity is unknown: "UnspecifiedKeyName" (firstCompositeKeyMember.Key in your example)

I had a simulair problem yesterday, i just want to know the identity field/property name of an object. I end up with 3 loops on the Metadata.PersistentTypes (1 for looping entities and stop if I found the name, 1 for looping it's members and 1 for looping each member to see if it is the identity... because none of the collections support other ways... for ie: Metadata.PersistentTypes("Customers")

So, to sum up:
I just want to know, for T what are it's indentity credentials. What prop names and types there are.

The test snip i now have is like this:

Public Function GetSingle(Of T)(aint_PKvalue As Integer, Optional abln_ReturnNothingOnError As Boolean = True) As T
    If abln_ReturnNothingOnError Then On Error Resume Next

    Dim objectKey As ObjectKey = New ObjectKey(GetType(T).FullName, aint_PKvalue) ' <<< this is the problem I think
    Dim objectKeyValues = objectKey.ObjectKeyValues
    If objectKeyValues.Count = 1 Then
        Dim firstCompositeKeyMember As ObjectKeyMember = objectKeyValues(0)
        Dim firstCompositeKeyMemberType As Type = firstCompositeKeyMember.Value.[GetType]()
        Select Case firstCompositeKeyMemberType.FullName
            Case "System.Int32"
                Return Me.GetObjectByKey(Of T)(New ObjectKey(GetType(T).FullName, CType(aint_PKvalue, System.Int32)))
            Case "System.Int64"
                Return Me.GetObjectByKey(Of T)(New ObjectKey(GetType(T).FullName, CType(aint_PKvalue, System.Int64)))
            Case Else
                Throw New Exception("Function '" & Me.Name & "'.'GetSingle(Of T)(..' encountered an entity identity with an unhandled type '" & firstCompositeKeyMemberType.FullName & "'. (1104061930)")
        End Select
    Else
        Throw New Exception("Function '" & Me.Name & "'.'GetSingle(Of T)(..' can only be used on single field identities. (1104061930)")
    End If

End Function

We'll get there :-)

Thanks!

[Patrice Boissonneault]

For multiple-field primary key, we came up with the following function which seems to work:

 

 

public T GetById<T>(IEnumerable<KeyValuePair<string, object>> key)

 

 

{

 

    T t;

 

 

 

    this.TryGetObjectByKey<T>(new ObjectKey(typeof(T).FullName, key), out t);

 

 

 

 

    return t;

 

 

}

And we call it this way:

EntityType1 e = Context.GetById<EntityType1>(new Dictionary<string, object>() { { "idfield1", IDValue1 }, { "idfield2", IDValue2 } });

That seems to work for us.

 

[Erik]

Thanks Patrice, that is somewhat simpler indeed, using the TryGetObjectByKey

Still got the issue int32/int64 though.. (in my case, not a multiple field identity)

[Petko_I]

Hello Erik,

You can obtain full information regarding the identity members of a persistent type by using out Metadata API. As far as we understand you have used something similar to this method to obtain the identity members by having the Clr type of an entity:

Public Function GetIdentityMembers(Of T)() As List(Of MetaPrimitiveMember)
    If Me.Metadata Is Nothing Then
        Return Nothing
    End If

    Dim type As Type = GetType(T)
    Dim persistentType As MetaPersistentType = Me.Metadata.PersistentTypes.FirstOrDefault(Function(x) String.Equals(x.Name, type.Name))
    Return MetadataWorker.GetIdentityMembers(persistentType)
End Function

This method is expected to be included as an extension in the partial class definition of the OpenAccessContext derived type. The advantage here is that you can use the static method GetIdentityMembers() in our MetadataWorker helper class. The class provides useful methods for working with our Metadata.
We are, however, a bit confused as to why you would like to check the type of the value you provide in the method GetSingle(). The value provided in the arguments list is Integer (Int32) by definition. You have mentioned in one of your previous posts that you are having issues with a big integer. Can you elaborate more on that matter? The ObjectKeyMember.Key real value as you have correctly noticed is not available when you create a new ObjectKey instance. However, if you just need to evaluate the types of the identity fields you can do so with reflection without knowing the names of the properties behind the identity values. Yet, details concerning the identity members can be obtained with the Metadata API as shown in the example above.

We are looking forward to resolving the issue.

Greetings,
Petko_I
the Telerik team

[Erik]

Hi Petko,

Yes, this function does what you say. I can see the identity column(s) and can see its SQL and ado type. Also I can see the property name for the entity, so I can see the entity identity column property type. Thanks for that.

But, you are right. Although I need it for a second issue as well, initially it started out for an other reason:
In the prev function, retrieving a single record, and filling the indentity pk value for function GetObjectByKey resulted in problems. Because I have (always, in this case) single column identity, but the can be of SQL type int or bigint. When passing a vb system.int32 to this function, while the SQL type is a bigint, i got an error. (I used overloads) The other way around as well. That's why I wanted to know the entity or SQL type of the identity.

I now also need it in some functions that do general handling and need to know what the identity column name is, but that's an other case.

Thanks for the info Petko.

[Petko_I]

Hi Erik,

We are glad we could help. If you still have questions regarding the use of the OpenAccess Metadata API or the ObjectKey, feel free to contact us.

Best wishes,
Petko_I
the Telerik team