How do i avoid getting duplicate results in Entity framework

Question

Hi i am using following code to fetch userID present in Gateway_users table to Appterms table. But problem is whenever i run solution I am getting duplicate records, that is first time 100 records with ID's and second time 200 records with duplicate ID's so on.

 public class HomeController : Controller
  {
    private AppMarketplaceEntities db = new AppMarketplaceEntities();
    private InstallTrackerEntities db1 = new InstallTrackerEntities();

    public ActionResult Index()
    {      
   List<int> gatewayUserId = new List<int>();

        using (var ctx = new InstallTrackerEntities())
        {
            gatewayUserId = ctx.Gateway_Users.Select(f => f.GatewayuserUID).ToList();
        }
        using (var ctx2 = new AppMarketplaceEntities())
        {
            foreach (var id in gatewayUserId)
            {

                ctx2.AppTerms.Add
                    (new AppTerm(){ GatewayuserUID = id });
            }
            ctx2.SaveChanges();
        }

        return View();  
     } } }

So what changes I have to make to above code to get only ID's which are present in Gateway_users table and it should fetch only once and not duplicate records.

@KarlAnderson It is user id who are already registered.It is in table called Gateway_users and I already have ID'S OF users.Now I need to copy that to Terms table which has column name exactly same as GatewayuserUID. But when i use above code I am, every time I am getting ID'S populated to table which results in duplicate records. — Dev, Aug 19 '13 at 15:04
Do you mean that the `gatewayUserId` collection contains duplicate values after the query (hard to believe if `GatewayuserUID` is the primary key) or do you mean that the `AppTerms` table gets filled up with records that have the same `GatewayuserUID` (not surprising because you are inserting new records everytime you enter the `Index` action)? — Slauma, Aug 19 '13 at 15:46
@Slauma your second guess is correct.I am having appterms table filled with duplicate records every time I run code. — Dev, Aug 19 '13 at 15:52
OK, then Pedro.The.Kid's answer below is probably a solution. The only thing that would still worry me is that you are doing this all of the time when you hit the `Index` GET action. It seems to me that an `AppTerm` should be created immediately together with a `GatewayUser`. — Slauma, Aug 19 '13 at 16:01
@Slauma Hi I will follow Kid's solution.But i dont understand what you said in end.could you please explain me with code if you dont mind.. — Dev, Aug 19 '13 at 16:16

Pedro.The.Kid · Accepted Answer · 2013-08-20T12:29:04.233

2

What is happening is you are always adding the InstallTrackerEntities GatewayuserUID List to the AppMarketplaceEntities AppTerms List and what you want is to only insert the new ones.

first if you want to remove the ones ho no longer exist in InstallTrackerEntities the easiest way is to just clear the AppTerms table and insert the new list as Tony Lunt says.

to just insert the ones ho are new create a list of the ones already in AppMarketplaceEntities and only insert the ones not present

    using (var ctx = new InstallTrackerEntities())
    {
        gatewayUserId = ctx.Gateway_Users.Select(f => f.GatewayuserUID).ToList();
    }
    using (var ctx2 = new AppMarketplaceEntities())
    {
        var appTermsUIDs = ctx2.AppTerms.Select(f => f.GatewayuserUID).ToList();

        foreach (var id in gatewayUserId.Where(e => !appTermsUIDs.Contains(e)))
        {
            ctx2.AppTerms.Add(new AppTerm(){ GatewayuserUID = id });
        }
        ctx2.SaveChanges();
    }

edited Aug 20 '13 at 12:29

answered Aug 19 '13 at 15:48

Pedro.The.Kid

1,968
1
14
18

Hi Pedro, I used your solution and it worked.what exactly i did is as you told, i ran my previous code only once just to insert all the records of GatewayUserId from gateway_users to appterms GatewayUserId.After that I commented that code and I used your solution.Now I cant see any duplicate records.So next time when there is a new record for GatewayUserId in gateway_users,it will be updated in appterms table also right? – Dev Aug 19 '13 at 16:37
@Dev yes it will, and you didn't need to run your previous code as if the AppTerms is empty all records will be inserted. – Pedro.The.Kid Aug 20 '13 at 08:30
In the above code we nowhere referring to InstallTrackerEntities database. But when new user registered, their GatewayUserId will be stored in this database.So how it will copy this new ID into Appterms table? Can you please explain? – Dev Aug 20 '13 at 10:50

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

You need to clear your second table (ctx2.AppTerms) if you're wanting to receive only new ID's every time. Otherwise, you're just appending new data on top of your old data. That would explain the "duplicates". For an example of this, see this answer: https://stackoverflow.com/a/15220460/1634770. Basically, you need to do something like this:

var objCtx2 = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)ctx2).ObjectContext;
objCtx2.ExecuteStoreCommand("TRUNCATE TABLE AppTerms");

edited May 23 '17 at 11:49

Community

1
1

answered Aug 19 '13 at 15:10

Tony Lunt

1

but this will delete all rows in that table. But I want fetch all the data present in GatewayuserUID to Terms table which has column called GatewayuserUID. Now I am able to do this but with this I am getting duplicate records.So how can i avoid this? – Dev Aug 19 '13 at 15:19

How do i avoid getting duplicate results in Entity framework

2 Answers2