MJoin Not working - Help Needed - C#

nfite

Hi Allan,

I am trying to link tables using MJoin for a one-to-many relationship using 3 tables one of which is a link table...

the tables are

Item, MCU, and MCULink (Being my link table)
Which are posted in the model below...

My goal is to populate a field and have proper relationships in order to handle a multiselect checkbox or dropdown list...

Below is my Model

    public class ItemGenModel1
    {

        #region Instance Properties

        public class Item
        {
            public int ItemID { get; set; }

            public string SKU { get; set; }

            public string UPC { get; set; } '''....

       }
         public class MCU
        {
            #region Instance Properties

            public int id { get; set; }

            public string Name { get; set; }

            public string Description { get; set; }

            #endregion Instance Properties
        }

        public class MCULink
        {
            #region Instance Properties

            public int LinkId { get; set; }

            public int MCUID { get; set; }

            public int ItemID { get; set; }

            #endregion Instance Properties
        }
    }

Below is my code in my Controller

        public IHttpActionResult ProjectItem(int projectid)
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;



            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response =
                new Editor(db, "Item", "ItemID")

                .Model<ItemGenModel1>()




                    .TryCatch(false)

                    .Field(new Field("Item.SRP4")
                    .Options("SRP4ProductMacroFamily", "SRP4ProductMacroFamily.id", "Description")
                    .Validator(Validation.DbValues(new ValidationOpts { Empty = true, Message = "Cannot be empty" }))

                    ) '''.......

          '''......
            .LeftJoin("SRP4ProductMacroFamily", "Item.SRP4", "=", "SRP4ProductMacroFamily.id")
            .LeftJoin("SRP2ProductClass", "Item.SRP2", "=", "SRP2ProductClass.id")
            .LeftJoin("SRP6MicroDivision", "SRP6MicroDivision.id", "=", "Item.SRP6")

         '''.....
                    .MJoin(new MJoin("MCU")
                    .Link("Item.ItemID", "MCULink.ItemID")
                    .Link("MCU.id", "MCULink.MCUID")
                    .Model<ItemGenModel1.MCU>()
                    .Field(new Field("id").Options("MCU", "id", "Name"))

                    )

                     .Where("ProjectItem.ProjectID", projectid)


                    ;

When running this code i receive a SQL Exception Error

The multi-part identifier "MCU.id" could not be bound.
The multi-part identifier "MCU.Name" could not be bound.
The multi-part identifier "MCU.Description" could not be bound.
The multi-part identifier "MCULink.LinkId" could not be bound.
The multi-part identifier "MCULink.MCUID" could not be bound.
The multi-part identifier "MCULink.ItemID" could not be bound.

We are currently running datatables version 1.5.5.0

Any help or guidance would be very appreciated.
Thanks
Nick

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Hi Nick,

Could you remove the MCU inner class from the ItemGenModel1 class please? You'd need to introduce a new class somewhere else that is used for the MCU with the Mjoin. The class given as the model to the top level Editor should only contain information about fields that it selects directly and left join fields.

More information about this is available here and the JoinArrayController.cs in the Editor .NET demos is an implementation of that.

Regards,
Allan

allan

I should also say: the same applies to the link table MCULink.

nfite

Thank you Allan!!

Your recommendation worked great we are in business!

Thanks again!