ADO.NET EF solution for "The table/view ' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view."

Hey there,

In my current project we are using the ADO.NET Entity Framework (for multiple run-time environments: .NET & COM - see previous post for details) and we got the error from the title when generating the entities from database:The table/view '' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.
In order to fix this, you should do this:
- create a non-nullable PK in a table.
- create a virtual non-nullable "PK" and ID in a view.

For Micosoft SQL Server you can use the ROW_NUMBER() function and for Oracle (11g) you can use rownum but that's not enought (I think for MS SQL Server also). You have to add a constraints (yes, views can have constraints):

CREATE OR REPLACE FORCE VIEW "SCHEMA"."VIEW_NAME" ("ID", "COL1", "COL2")
AS
SELECT ROWNUM, COL1, COL2 FROM TABLE
WITH READ ONLY;

alter view VIEW_NAME add constraint id_pk primary key (ID) disable novalidate;.

 Hope it helps you sometime. :)

Bonus tip: if your view does a UNION (as it was in my case) the second SELECT will have something like SELECT ROWNUM+(SELECT COUNT(*) FROM TABLE1). ;)

[COM] Pass .NET object array to COM

Hey there,
In the life of a .NET developer comes a day when is needed to interact with COM. In this post I will try to explain a way to pass .NET array of objects (can be common CLR objects) to COM environment.
First, you have to expose the .NET object(s) to COM (I will name it Type) by implementing an interface (IType) and decorating them as follows:
[ComVisible(true)]
[System.Runtime.InteropServices.InterfaceTypeAttribute(System.Runtime.InteropServices.ComInterfaceType.InterfaceIsDual)]
[Guid("104136e6-af1f-4719-985a-8b65e588e4bb")]
public interface IType


[ComVisible(true)]
[System.Runtime.InteropServices.ClassInterfaceAttribute(System.Runtime.InteropServices.ClassInterfaceType.None)]
[Guid("85aff115-b4ad-473a-9774-285cb1b9ef28")]    [System.Runtime.InteropServices.StructLayoutAttribute(System.Runtime.InteropServices.LayoutKind.Sequential)]
public partial class Type : IType

Also, you will need some kind of context where all the operations will be hosted. This context, which is a normal C#/.NET class, will also have to be exposed to COM.
Now, lets say you want to pass an IType[] to COM. If you write your method from the context to return an IType[] (public IType[] GetData(string filter)), in the .tlh file (that is generated for C++) the method will have a parameter SAFEARAY**. You could use this but you will have to know the dimension of the array before invoking the GetData(...) method (plus I don't think you will be able to see the COM types in the SAFEARRAY when you'll write your code).

My solution is using the callback idea. I will write the method in the .NET context returning just an IType and using some magic in C++ and C#/.NET I will invoke GetData method multiple times and return the elements one by one. I will also exppose a boolean isFinished parameter as output.
In the .NET code, in order to maintain the state of some variable (like static in C/C++ functions) you can use [ThreadStaticAttribute] or ThreadLocal like this:
internal static class GetMany<T,IT>
        where T : class
        where T : IT
{
     [ThreadStatic]
     private static int counter = 0;

     [ThreadStatic]
     private static IList<IT> collection;

     public static IT Get(ObjectQuery<T> query, string predicate, string orderby, out bool isFinished)
     {
         isFinished = true;

         if (collection == null)
         {
             if (!string.IsNullOrEmpty(predicate))
                 query = query.Where(predicate);

             if (!string.IsNullOrEmpty(orderby))
                 query = query.OrderBy(orderby);

             collection = query.Cast<IT>().ToList();
         }

         if (collection == null || counter >= collection.Count())
         {
             collection = null;
             counter = 0;
             return default(IT);
         }

         isFinished = false;
         return collection[counter++];
     }
}
In the context method GetData I will invoke the method Get from GetMany like this:
GetMany<Type, IType>.Get(collection, predicate, orderby, out isFinished);
In my project case the collection was an ADO.NET EF collection and the predicate and orderby strings were passed from C++.

In .tlh file you will get a function like HRESULT GetData(BSTR predicate, BSTR orderby, VARIANT_BOOL* isFinished, IType* entity); where IType is:
struct __declspec(uuid("104136e6-af1f-4719-985a-8b65e588e4bb"))
IType : IDispatch.

All we need to do now is to write some code to invoke the GetData method multiple times. For this, since I am using VS2010 with some of the C++ 11 features supported (e.g. auto, lambdas, static_assert, etc), I will write a templated function using a std::function as a callback (just like Func if I may :) ):

template<typename T>
void import(std::vector<T>& container, std::function<HRESULT (VARIANT_BOOL* isFinished, T& result)> action)
{
    VARIANT_BOOL isFinished = FALSE;

    while (!isFinished)
    {
        T result;
        if (!SUCCEEDED(action(&isFinished, result)))
            throw;

        if (result != nullptr)
            container.push_back(result);
    }
}


When I want to invoke the actual code, I would write:
std::vector<ITypePtr> collection;         
import(collection ,
[&ptr, &predicate, &orderby] (VARIANT_BOOL* isFinished, ITypePtr& result)->
        HRESULT {
            auto hr = testResult.CreateInstance(__uuidof(Type));
            return ptr->GetData(predicate, orderby, isFinished, &result);  
});


After this line, the collection will contain all ITypePtr passed from .NET.

That's all! No more SAFEARRAY**! :)