how to update Many to Many table in Asp.Net MVC 5 with Identity table and Custom table

Question

i need to make many to many relation with Identity table and Custom table and i have used the same logic in this answer but the problem i can not add anything to the new many to many table

here is my own code

ApplicationUser

public class ApplicationUser : IdentityUser
{
    public ApplicationUser()
    {
        subjects = new HashSet<subject>();
    }

    public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
    {
        // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
        var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
        // Add custom user claims here
        return userIdentity;
    }
    public virtual ICollection<subject> subjects { get; set; }
}

the subject

 public class subject
    {        
        public int subjectId { get; set; }
        public string subjectCode { get; set; }
        public virtual ICollection<ApplicationUser> buyers { get; set; }
    }

the function i am tiring to add entity with suppose to take a email of user and subjectCode and link the user and the subject together in the table

public ActionResult addBuyer(FormCollection fc)
{
    ApplicationUser tst = _context.Users.FirstOrDefault(b => b.Email.Equals(fc["Email"].ToString()));
    var temp = _context.subjects.Include("buyers").Where(c => c.subjectCode.Equals(fc["SubCode"].ToString()) );
    temp.First().buyers.Add(tst);
    return RedirectToAction("Index");
}

and this is the error

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'System.String get_Item(System.String)' method, and this method cannot be translated into a store expression.

score 0 · Accepted Answer · edited May 23 '17 at 12:25

Since fc["Email"] and fc["SubCode"] collection indexers possibly have no SQL syntax equivalent on LINQ to Entities, instead try assigning/extracting plain values from indexers into string variables and pass them into query:

public ActionResult addBuyer(FormCollection fc)
{
    // assign ToString() methods into string variables
    String email = fc["Email"].ToString();
    String subCode = fc["SubCode"].ToString();

    // pass only plain values into the query
    ApplicationUser tst = _context.Users.FirstOrDefault(b => b.Email.Equals(email));
    var temp = _context.subjects.Include("buyers").Where(c => c.subjectCode.Equals(subCode));
    temp.First().buyers.Add(tst);
    return RedirectToAction("Index");
}

NB: You should avoid ToString() method call inside LINQ query as mentioned in this post, use SqlFunctions.StringConvert or some other aggregate methods as alternative way.

Related issues:

LINQ to Entities does not recognize the method 'System.String get_Item (System.String)',

LINQ to Entities does not recognize the method 'System.String get_Item(System.String)' method, and this method cannot be translated into a store expression

how to update Many to Many table in Asp.Net MVC 5 with Identity table and Custom table

1 Answers1