Relating unrelated data entities

Sep 30, 2011 at 11:15 PM

Hello,

Firstly, I noticed you don't have "donate" links to a paypal account or such like many other open source projects do. I am going to build a large system with your framework as the skeleton so I feel I owe you a debt of gratitude. Anything that keeps people like you creating open source gems like this is worthy of any bit of support, imo

Secondly, here's my actual question. This could just be poor modeling on my part, but I'm not sure how to best approach this scenario within the MVC framework in the context of the Prodinner architecture.

  1. I have Clients entity (meals became this)
  2. I have Applications entity (dinners became this)
  3. I added a Families entity
  • Clients have many to many relationships with both Applications and Families. Applications and Families have no direct foreign key relationship to eachother. (perhaps this is incorrect?)
  • I have the intermediary tables (ApplicationClients) and (FamilyClients)
  • I have the generic crud create page you made and am using it to add client(s) via Awesome Lookup with multiselect to both Applications and Families.

Here is the main issue--when I come to add clients to Applications, this is how I want it to work:

  • Application create/edit screen has a PrimaryClientId lookup in which user selects ONE client in the system
  • Application create/edit screen has a "Associated Clients/Applicants" lookup, which must somehow do a lookup by the PrimaryClientID into the Families table and pull all of that primaryClient's family members into the "Associated Clients/Applicants" lookup
  • Once the family members are loaded into the Associated Clients/Applicants lookup, the user can add/remove via the multi select just like normal
  • On form submit, these family members now become the associated Clients/Applicants and become added to the relevant table (ApplicationClients)

I hope I have explained this clearly. What would be the best strategy to approach this problem within the Prodinner/project awesome framework? Adding multiple view models? Changing my data model? Simply creating more advanced LINQ queries?


Normally I would resolve this through stored procedures but I'm a bit clueless as to what to do in the MVC world.

Thanks so much.

Oct 1, 2011 at 5:11 PM
Edited Oct 1, 2011 at 5:17 PM

so far I've been trying to replace my Clients/participants lookup controller's search action with this:

 

  [HttpPost]
        public ActionResult Search(string search, IEnumerable<int> selected, IEnumerable<int> householdmembersIds, int page)
        {
            const int pageSize = 9;
            var result = r.Where(o => o.LastName.Contains(search) || o.FirstName.Contains(search)
                || o.Ssn.StartsWith(search));
                result = result.Where(o => householdmembersIds == null 
                || (o.Households != null && o.Households.Select(x => x.Participants.Select(g => g.Id).Intersect(householdmembersIds).Count() != 0))
                .OrderByDescending(o => o.Id)
                .Where(o => selected == null || !selected.Contains(o.Id));

            var rows = this.RenderView(@"Awesome\LookupList", result.Skip((page - 1) * pageSize).Take(pageSize));

            return Json(new { rows, more = result.Count() > page * pageSize });
        }

 

I'm basically trying to only search for clients who are also members of households. I'm sure I'm screwing up the syntax of the query, but I'm not even sure what kind of syntax that is.

I thought it was LINQ to SQL but that looks quite different.

 

These are my entities:

 public class Participant : DelEntity
    {
        ...
        public virtual ICollection<Application> Applications { get; set; }
        public virtual ICollection<Household> Households { get; set; } 
    }

 public class Application : DelEntity
    {
 	...
        public virtual Participant Participant { get; set; } <- primary applicant, single ID
        public virtual ICollection<Participant> Participants { get; set; }  <- all other applicants 
    }

  public class Household : DelEntity
    {
        ...
        public virtual ICollection<Participant> Participants { get; set; }
    }

Oct 1, 2011 at 7:37 PM
Edited Oct 1, 2011 at 10:00 PM

Trying a parent-bound ajax dropdown for the moment, having trouble with that too.

Here is my controller, input, and the call

I inserted break point and the parentId is correctly being passed to the controller, after which I get an "object is set to a null reference instance" error

 

the controller:::::::::::::::::::::::::::::::::::::::::
  
public class PrimaryApplicantAjaxDropDownController : Controller
    {
        private readonly IRepo<Application> a;
        private readonly IRepo<Participant> p;

        public PrimaryApplicantAjaxDropDownController(IRepo<Application> a, IRepo<Participant> p)
        {
            this.a = a;
            this.p = p;
        }

        public ActionResult GetItems(int? key, int? parent)
        {
            var list = new List<SelectListItem> { new SelectListItem { Text = "not selected", Value = "" } };
            var source = parent.HasValue ?
                a.Get(parent.Value).Participants
                : p.GetAll();

            list.AddRange(
                source.Select(o => new SelectListItem
                {
                    Text = o.LastName + ", " + o.FirstName,
                    Value = o.Id.ToString(),
                    Selected = o.Id == key
                }));
            return Json(list);
        }
    }

the input::::::::::::::::::::::::::::::::::::::::::::

  public class ApplicationInput : Input
    {
...

        [UIHint("Lookup")]
        [Lookup(Multiselect = true, Fullscreen = true, Paging = true, ClearButton = true)]
        [Display(ResourceType = typeof(Mui), Name = "Additional_Applicants")]
        public IEnumerable<int> Participants { get; set; }

        [UIHint("AjaxDropdown")]
        [Display(ResourceType = typeof(Mui), Name = "Primary_Applicant")]
        public int? ParticipantId { get; set; }
...
    }

the call::::::::::::::::::::::::::::::::::::::::::::::
@Html.AjaxDropdown("PrimaryApplicant", parentId: "Participants")

No idea what's wrong here, followed example in Project Awesome live demo to a T, the only difference is my parent controller is a lookup, not another dropdown.

Full Error Text:

type="System.NullReferenceException" message="Object reference not set to an instance of an object." source="Omu.ACIM.WebUI" detail="System.NullReferenceException: Object reference not set to an instance of an object. at Omu.ACIM.WebUI.Controllers.ParticipantIdAjaxDropDownController.GetItems(Nullable`1 key, Nullable`1 parent) in C:\dev\ACIM\ACIM\WebUI\Controllers\ParticipantIdDropdownController.cs:line 24 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.<>c__DisplayClass17.<InvokeActionMethodWithFilters>b__14() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) at System.Web.Mvc.Controller.ExecuteCore() at System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) at System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) at System.Web.Mvc.MvcHandler.<>c__DisplayClass6.<>c__DisplayClassb.<BeginProcessRequest>b__5() at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass1.<MakeVoidDelegate>b__0() at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End() at System.Web.Mvc.MvcHandler.<>c__DisplayClasse.<EndProcessRequest>b__d() at System.Web.Mvc.SecurityUtil.<GetCallInAppTrustThunk>b__0(Action f) at System.Web.Mvc.SecurityUtil.ProcessInApplicationTrust(Action action) at System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) at System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep

Oct 1, 2011 at 10:25 PM

Thought that maybe the call and invocation was wrong.

Changed the call + the input.cs

call::::::::::
<div class="inputarea">      
            @Html.EditorFor(o => o.Participants)
            @Html.EditorFor(o => o.ParticipantId)
</div>
input.cs:::::
[UIHint("Lookup")]
        [Lookup(Multiselect = true, Fullscreen = true, Paging = true, ClearButton = true)]
        [Display(ResourceType = typeof(Mui), Name = "Additional_Applicants")]
        public IEnumerable<int> Participants { get; set; }

        [Req]
        [UIHint("AjaxDropdown")]
        [Display(ResourceType = typeof(Mui), Name = "Primary_Applicant")]
        [AjaxDropdown(ParentId="Participants")]
        public int? ParticipantId { get; set; }

same exact error

Oct 2, 2011 at 12:38 AM

Got it. The parent needed to be a collection, not an int? as I was stupidly doing. Also the list needs to initialize as empty.

Here is the code. 

  public ActionResult GetItems(int? key, IEnumerable<int> parent)
        {
            if (parent == null) return new EmptyResult();
           
            var res = r.GetAll().Where(o => parent.Contains(o.Id))
                .Select(o =>
                                                new SelectListItem
                                                {
                                                    Text = string.Format("{0}, {1}", o.LastName, o.FirstName),
                                                    Value = o.Id.ToString(),
                                                    Selected = o.Id == key
                                                });

            return Json(res);
        }

 

Now my only remaining issue for this entire thread is finding out how to pull participants from the household entity, guessing some kind of join syntax perhaps.

Oct 3, 2011 at 11:05 PM

update on this: the difficulty i was having was that I needed to reach inside a lookup table that isn't part of my entity models

i.e.

a many to many relationship creates an intermediary table that has the primary key from each parent table. i needed to retrieve client IDs from this table

I just passed in a stored procedure

it doesnt seem like a very ideal entity framework/MVC-ish solution but I could not figure out how to do it within this framework.

my issues are solved either way