Editor .NET C# MVC: LeftJoin causing exception '

david.j.meyer2@boeing.com

Link to test case:
Debugger code (debug.datatables.net):
Controller Code

try
{
    using (var db = new Database("oracle", connectionString))
    {
        Editor editor = new Editor(db, "CADATS.SDASH_REPOSITORY", "SDASH_REPOSITORY.SDAID")
            .TryCatch(false)
            .Debug(true)
                .Model<SDASH_REPOSITORY>()
                .LeftJoin("SDASH_REPOSITORY", "SDASH_REPOSITORY.SDAID", "=", "SDASH_REPOSITORY_TECH.SDAID")
                .Model<SDASH_REPOSITORY_TECH>();

        if (String.IsNullOrEmpty(said))
        {
            response = editor
                .Process(request).Data();
        }
        else
        {
            response = editor.Where("SDAID", said).Process(request).Data();
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine("Error getting repository data: " + ex.Message);
}

Model of Primary Table

    public class SDASH_REPOSITORY 
    {
        public string SDAID { get; set; }
        public string PART_NUMBER { get; set; }
        public string PART_NAME { get; set; }
        public string THEPROGRAM { get; set; }
        public string DIVISION { get; set; }
        public string SUB_PROGRAM { get; set; }
        public string BEST_CODE { get; set; }
        public string SUPPLIER { get; set; }
        public string MNGT_DOC { get; set; }
        public string MNGT_DOC_REV { get; set; }
        public string MNGT_DOC_TYPE { get; set; }
        public string MNGT_DOC_UNINCORPORATED_RCPS { get; set; }
        public string DATA_DOC { get; set; }
        public string DATA_DOC_REV { get; set; }
        public string DATA_DOC_TYPE { get; set; }
        public string DATA_DOC_UNINCORPORATED_RCPS { get; set; }
        public string RECORD_NOTES { get; set; }
        public string CREATED_BY_BEMSID { get; set; }
        public string CREATED_BY_NAME { get; set; }
        public string CREATED_ON { get; set; }
        public string MODIFIED_BY_BEMSID { get; set; }
        public string MODIFIED_BY_NAME { get; set; }
        public string MODIFIED_ON { get; set; }
        public string LAST_UPDATE { get; set; }
    }

Model of Secondary table which can contain 0 to N number of records for a specific SDAID value

    public class SDASH_REPOSITORY_TECH 
    {
        public Int64 CREATED_BY_BEMSID {get;set;}
        public string CREATED_BY_NAME {get;set;}
        public DateTime CREATED_ON { get;set;}
        public Int64 LAST_UPDATE { get;set;}
        public Int64 MODIFIED_BY_BEMSID { get;set;}
        public string MODIFIED_BY_NAME { get;set;}
        public DateTime MODIFIED_ON { get;set;}
        public Int64 SDAID { get;set;}
        public string TECH_DOC { get;set;}
        public Int64 TECH_DOC_ID { get;set;}
        public string TECH_DOC_REV { get;set;}
        public string TECH_DOC_TYPE { get;set;}
        public string TECH_DOC_UNINCORPORATED_RCPS { get;set;}
    }

Error messages shown: An item with the same key has already been added.
Description of problem:
I am trying to perform a left join using the SDAID field.

.LeftJoin("SDASH_REPOSITORY", "SDASH_REPOSITORY.SDAID", "=", "SDASH_REPOSITORY_TECH.SDAID")

When I execute the statement

response = editor.Process(request).Data();

I get an exception stating "an item with the same key has already been added". The secondary table (SDASH_REPOSITORY_TECH) can have 0 to N records for a given SDAID. Any suggestions on how to resolve the problem is greatly appreciated.

allan

Both models have properties which have the same name - e.g. CREATED_ON. When the model is added to Editor, it just adds it as a plain key. What you need to do is provide the table name so they can be distinguished between. You do that by passing in the table name as a string parameter to the Model() method - e.g.:

.Model<SDASH_REPOSITORY>("SDASH_REPOSITORY")
.Model<SDASH_REPOSITORY_TECH>("SDASH_REPOSITORY_TECH");

That will mean you will need to update your DataTables columns.data and Editor's fields.name properties to reflect the table name as well.

More information about this is available in the .NET join documentation for Editor.

Allan

david.j.meyer2@boeing.com

I included the table names in the column definitions but I also had to change the Editor instantiation too. I have included the changes below to help anyone else that runs into this issue.

                    Editor editor = new Editor(db, "CADATS.SDASH_REPOSITORY", "SDAID")
                        //.TryCatch(false)
                        .Debug(true)
                            .Model<SDASH_REPOSITORY>("SDASH_REPOSITORY")
                            .Model<SDASH_REPOSITORY_TECH>("SDASH_REPOSITORY_TECH")
                            .Field(new Field("SDASH_REPOSITORY.SDAID"))
                            .Field(new Field("SDASH_REPOSITORY.DIVISION").SetFormatter(Format.NullEmpty()))
                            .Field(new Field("SDASH_REPOSITORY.CREATED_ON")
                                .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601))
                                .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_8601)))
                            .Field(new Field("SDASH_REPOSITORY.MODIFIED_ON")
                                .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601))
                                .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_8601)))
                            .LeftJoin("SDASH_REPOSITORY_TECH", "SDASH_REPOSITORY.SDAID", "=", "SDASH_REPOSITORY_TECH.SDAID")
                            .Field(new Field("SDASH_REPOSITORY_TECH.SDAID"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.CREATED_BY_BEMSID"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.CREATED_BY_NAME"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.CREATED_ON"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.LAST_UPDATE"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.MODIFIED_BY_BEMSID"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.MODIFIED_BY_NAME"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.MODIFIED_ON"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.TECH_DOC"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.TECH_DOC_ID"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.TECH_DOC_REV"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.TECH_DOC_TYPE"))
                            .Field(new Field("SDASH_REPOSITORY_TECH.TECH_DOC_UNINCORPORATED_RCPS"));

We have decided to go a different direction for presenting the related data. We are simply going to populate a child table. It will have it's own editor to provide the curd operations.

I then started receiving Oracle error codes so I removed the

\\.TryCatch(false)

trycatch setting to false. This allowed me to examine the select statements in Toad to determine why they were failing. That was very useful for figuring out the remaining issues with my implementation.

allan

Great to hear you got a solution. Thanks for letting me know.

Allan