locked
EF returning two identical records when 2 different records are in the table. RRS feed

  • Question

  • User1779468316 posted

    I have this method in my Music Entities class:

    public static List<Music> getMusic( decimal id, IDataProvider db )
    {
      List<Music> data = db.MusicData.Where( m => m.id == id ).ToList<Music>();
     return data;
    }

    There are only 2 records in the database both with the same id field.

    But this code returns two copes of the first of the two records.

    How can I get all records for this id number?

     

     

    Thursday, December 6, 2012 8:21 PM

All replies

  • User1492915735 posted

     

    public static IEnumerable<Music> getMusic( decimal id, IDataProvider db )
    {
       return db.MusicData.Where( m => m.id == id ).ToList();
    }

    Friday, December 7, 2012 1:48 AM
  • User1779468316 posted

    public static IEnumerable<Music> getMusic( decimal id, IDataProvider db ) { return db.MusicData.Where( m => m.id == id ).ToList(); }

    That generates a comple time error:

    Error 1 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<My_MSI.Net.Models.Entities.Music>' to 'System.Collections.Generic.List<My_MSI.Net.Models.Entities.Music>'. An explicit conversion exists (are you missing a cast?) My-MSI.Net\Controllers\AdminBackOfficeController.cs 260 28

    Friday, December 7, 2012 5:11 AM
  • User1779468316 posted

    Even if using:

    var data = db.MusicData.Where( m => m.id == id )

    When I hit the breakpoint I set and expand the results, I'm still seeing two identical records when I'm 100% positive that there are 2 different records in the database ( because when I look in the databse (SQL Server Managment Studio) I see two different records:

    Screaming Skin | No Exit | Blondie
    Maria | No Exit | Blondie

    Both records when I expand the results are:

    Maria | No Exit | Blondie
    Maria | No Exit | Blondie

    All fields (except for the binary field) are set as Primary Keys.

     

     

     

    Friday, December 7, 2012 8:42 AM
  • User1492915735 posted

    You don't show us all related code. E.G How do you implemetn IDataProvider?

    How do you implement the DAL? Did you hit the database somewhere?

     

    And seems like the code automaically added more items to the collection somewhere. That is why you got the same result more than 1 or 2 + because of the filter parameter is unique.

     

    Friday, December 7, 2012 10:25 AM
  • User1779468316 posted

    IDataProvider is simply an Interface class that specfies what methods the EFDbContext Class should implement.

    The EFDbContext class contains a set of DbSet<className> types and one method that has to do with setting up the connection string. Nothing exotic here at all.

    And I have NEVER used a Filter with any of my classes, Don't even understand how to create a FILTER on a class!

    Friday, December 7, 2012 10:45 AM
  • User1492915735 posted
    //Single
    public static Music getMusic( decimal id, IDataProvider db )
    {
       return db.MusicData.Find(id);
    }
    
     
    //All
    public static IEnumerable<Music> getMusic( decimal id, IDataProvider db )
    {
       return db.MusicData.ToList();
    }
    

    Friday, December 7, 2012 10:55 AM
  • User1779468316 posted

    The primary key used for this site is decimal because we expect to have a very large number of members and decimal seemed like the best choice for a primary key.

    The Music table is a bit different than the other tables, in that the id field is a members id number and while the number is unique it will not be unique within the Music table because a single member is not limited to uploading only one song at a time.

    That is why the Primary key on the Music table is a multi-part key:

    id
    songName
    album
    artist
    This covers all of the textual fields in the table, the only other field is called music and is of type varbinary(MAX).

    We do not expect a member to upload more than one copy of any song, therefore this mult-part key does provide uniquness.

    I have never applied a filter to any of my DB classes and even if I wanted to I wouldn't know how to do that.

    I use the id value because I want ALL of the Music data for this particual member.

    Friday, December 7, 2012 11:07 AM
  • User1492915735 posted

    I understood but could you show us?

     

    Your EFDbContext and your IDataProvider interface.

    theres's something wrong.

    Friday, December 7, 2012 11:20 AM
  • User1779468316 posted

    Okay, we have the IDataProvider, followed by the EFDbContext, following by Music and you can see that there are no filters applied.

    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Data.Objects;
    using System.Data.SqlClient;
    using System.Data.Objects.DataClasses;
    using System.Linq;
    using System.Text;
    using My_MSI.Net.Models.Entities;

    namespace My_MSI.Net.Models.Abstract
    {
      public interface IDataProvider
      {
         DbSet<Banned>          Banned          { get; set; }
      DbSet<Identity>        Identity        { get; set; }
      DbSet<Photos>          Photos          { get; set; }
      DbSet<Details>         Details         { get; set; }
      DbSet<BankData>        BankData        { get; set; }
      DbSet<Matrix>          MatrixData      { get; set; }
      DbSet<Contacts>        ContactData     { get; set; }
      DbSet<Product>         ProductData     { get; set; }
      DbSet<Applications>    Applications    { get; set; }
      DbSet<Suggestions>     Suggestions     { get; set; }
      DbSet<Testimonials>    TestimonialData { get; set; }
      DbSet<Payment>         PaymentData     { get; set; }
      DbSet<Transactions>    Transactions    { get; set; }
      DbSet<Downloads>       Downloads       { get; set; }
      DbSet<Blogs>           Blogs           { get; set; }
      DbSet<Logins>          Logins          { get; set; }
      DbSet<Register>        Registeration   { get; set; }
      DbSet<Removals>        RemovalData     { get; set; }
      DbSet<Autoresponder>   Autoresponder   { get; set; }
      DbSet<Music>           MusicData       { get; set; }
      DbSet<Earnings>        Earnings        { get; set; }
      
    DbSet<Withdrawls>      Withdrawls      { get; set; }
      DbSet<SponsorshipView> SponsorshipData { get; set; }
      DbSet<LogErrors>       LogErrors       { get; set; }
       }


    using System.Data.Entity.ModelConfiguration.Conventions;
    using System.Linq;
    using System.Web;
    using My_MSI.Net.Models.Entities;
    using My_MSI.Net.Models.Abstract;

    namespace My_MSI.Net.Models.Concrete
    {
      public class EFDbContext : DbContext, IDataProvider
      {
          public EFDbContext()
          {}

       public DbSet<Banned>          Banned          { get; set; }
       public DbSet<Identity>        Identity        { get; set; }
       public DbSet<Photos>          Photos          { get; set; }
       public DbSet<Details>         Details         { get; set; }
       public DbSet<BankData>        BankData        { get; set; }
       public DbSet<Matrix>          MatrixData      { get; set; }
       public DbSet<Contacts>        ContactData     { get; set; }
       public DbSet<Product>         ProductData     { get; set; }
       public DbSet<Transactions>    Transactions    { get; set; }
       public DbSet<Applications>    Applications    { get; set; }
       public DbSet<Suggestions>     Suggestions     { get; set; }
       public DbSet<Payment>         PaymentData     { get; set; }
       public DbSet<Testimonials>    TestimonialData { get; set; }
       public DbSet<Downloads>       Downloads       { get; set; }
       public DbSet<Blogs>           Blogs           { get; set; }
       public DbSet<Logins>          Logins          { get; set; }
       public DbSet<Register>        Registeration   { get; set; }
       public DbSet<Removals>        RemovalData     { get; set; }
       public DbSet<Autoresponder>   Autoresponder   { get; set; }
       public DbSet<Music>           MusicData       { get; set; }
       public DbSet<Earnings>        Earnings        { get; set; }
       public DbSet<Withdrawls>      Withdrawls      { get; set; }
          public DbSet<SponsorshipView> SponsorshipData { get; set; }
       
    public DbSet<LogErrors>       LogErrors       { get; set; }

      protected override void OnModelCreating( DbModelBuilder modelBuilder )
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        base.OnModelCreating( modelBuilder );
        }
      }
    }

    And my Music class is:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;
    using System.Web;
    using My_MSI.Net.Models.Abstract;
    using My_MSI.Net.Models.Concrete;

    namespace My_MSI.Net.Models.Entities
    {
      public class Music
        {
        public static IDataProvider db { get; set; }

        public decimal id       { get; set; }
            [Required( ErrorMessage = "Song Name is a requried field." )]
        public string  songName { get; set; }
            [Required( ErrorMessage = "Album is a required field." )]
        public string  album    { get; set; }
            [Required( ErrorMessage = "Artist is a required field." )]
        public string  artist   { get; set; }
        public byte[]  music    { get; set; }
            [NotMapped]
        public string  Status   { get; set; }
       
        public
    Music() 
            {
            }

        public Music( decimal Id, string SongName, string Album, string Artist, byte[] song, IDataProvider Db )
            {
                id       = Id;
                songName = SongName;
                album    = Album;
                artist   = Artist;
                music    = song;
     
              My_MSI.Net.Models.Entities.Music.db = Db;
              Db.MusicData.Add( this );
             ( ( EFDbContext)Db ).SaveChanges();
            }

        public static List<Music> getMusic( decimal id, IDataProvider db )
           {
         List<Music> data = db.MusicData.Where( m => m.id == id ).ToList<Music>();
         return data;
           }
       }
    }

    Now you've seen everything in the stack.

     

    Friday, December 7, 2012 12:11 PM
  • User1779468316 posted

    Even after dropping the Music table, and receating it, and putting the 4 filed index in place before any data was added, I'm still getting duplicated records in the result set.

     

    Friday, December 7, 2012 3:02 PM
  • User1779468316 posted

    I added a third song to the database and guess what?

    Now I'm getting THREE copies of the FIRST record from the database.

    It has correct record count but incorrect records.

    What's up with that?

    How can I fix this?

     

    Friday, December 7, 2012 3:39 PM
  • User1779468316 posted

    When I take the generated SQL statment and plug it into SQL 2008 R2's SQL Managemnet Studio, I get both expected records back.

    When I use this simplified SQL statemnet and plug it into SQL 2008 R2's SQL Management Studio, I get both expected records back:

    SELECT * FROM [dbo].[Music] where id = 1

    This is leading me to believe that this is a bug within EF.

    Can anybody reproduce this bug to confirm?

    Friday, December 7, 2012 4:37 PM
  • User3866881 posted

    List<Music> data = db.MusicData.Where( m => m.id == id ).ToList<Music>();

    Hello,

    Your problem is a little strange. Please change from decimal to int and have a try.

    If still fails, please import the Stored Procdure of your SqlCommand inside the EntityFramework and continue your work.

    Friday, December 7, 2012 10:03 PM
  • User1492915735 posted

    eric2820

    eric2820

    You did wrong with the architecture. You's better take a look at the IUnitOfWork and Repository.

    If you still continuos with this pattern you will never know where is the bug lives. Bugs will follow up

    public static List<Music> getMusic( decimal id, IDataProvider db ) { List<Music> data = db.MusicData.Where( m => m.id == id ).ToList<Music>(); return data; }
    
    
    
    
    
     
    This is not the right thing to do with "List<Music>" It should be IEnumerable<T> or ICollection<T> then return to List();
     
    Tolist mean forgoes the data after return. No overhead
     
    List<T> might add the data to the collection of List<T> that is why you get more than 1 or 2.
     
    If you try to create more instance of the music class you might get 3 item.

    Saturday, December 8, 2012 3:17 AM
  • User1779468316 posted

    You mean something like this:

    public static IEnumerable<Music> getMusic( decimal id, IDataProvider db )
    {
       Db = db;
     IEnumerable<Music> data = db.MusicData.AsQueryable();
     return data;
    }

    Well that has the same problem! The first record is repeated for the number of records in the table.

    When I run the qurey on SQL Managment Studio, I get both reccords, thus I'm beginning to think that this is a bug within EF.


     

    Saturday, December 8, 2012 6:22 AM
  • User1492915735 posted

    I don't think that is the EF bug. Could you repro then put it into skydrive then we can try?

    Saturday, December 8, 2012 6:27 AM
  • User1492915735 posted

     

    See I Repro your solution and it work find.

    "PracticeSolution"

    https://skydrive.live.com/?cid=B692C2CBAD70A05C&id=B692C2CBAD70A05C%21127

    You should correct your architecture.

    Saturday, December 8, 2012 7:36 AM
  • User1779468316 posted

    I have a test project up running and published, now all I need is a way to get it to you. The zip file is 834 KB in size.

    You can reach me at EircDill@outlook.com

     And here is the create script for the Music table.

    USE [My-MSI.Net]

    GO

    /****** Object:  Table [dbo].[Music]    Script Date: 12/08/2012 12:05:38 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Music](

    [id] [decimal]

    (18, 0) NOT NULL,

    [songName] [nvarchar]

    (50) NOT NULL,

    [artist] [nvarchar]

    (50) NOT NULL,

    [album] [nvarchar]

    (50) NOT NULL,

    [time] [datetime2]

    (7) NOT NULL,

    [music] [varbinary]

    (max) NOT NULL,

    CONSTRAINT [PK_Music] PRIMARY KEY CLUSTERED
    (
      [songName] ASC,
      [artist] ASC,
      [album] ASC,
      [time] ASC
    )

    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    )
    ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    Saturday, December 8, 2012 11:19 AM
  • User1779468316 posted

    I've put the zip file up on my Sky Drive

     And here is the link to the publisished.zip file:

    https://skydrive.live.com/redir?resid=4E9EAC14BC59A352!106&authkey=!AMO-kDNeLh924E0

    Saturday, December 8, 2012 11:39 AM
  • User1779468316 posted

    I've imlemented the MusicCollection : ICollection<Music> backed by a private List<Music> and I set a breakpoint on the Add( Music m ) member:

    What I saw was two idential records coming from the database!

    Yet when I put the query string in SQL Management Studio, I get two different records.

    What would you deduce from this result?

    I'm getting closer to blaming the Entity Framework for this and I'll contract Microsoft about this issue on Monday.

     

     

    Saturday, December 8, 2012 6:57 PM
  • User3866881 posted

    I'm getting closer to blaming the Entity Framework for this and I'll contract Microsoft about this issue on Monday.

    OK. Now I've submitted your strange question to a senior development team to analyze this. If they have free time, I think they'll reply to you;)

    Don't feel worried about that.

    Saturday, December 8, 2012 8:49 PM
  • User1779468316 posted

    Thank you.

    I've moved all the database access code to the Controller and guess what? It still doesn't work as well as SQL Server works.

    All I want is all the records for a given id number. That shouldn't be so unusual.

    In this case I only have 2 records in the table both with the same id number, but that will not always be the case so I need to pull the records by id number.

    You see when a member uploads music to the server, it is his/her id number that goes into the id field. Thus the id field can not be unique. The id field does have an index though.

     

    Sunday, December 9, 2012 5:48 AM
  • User1779468316 posted

    This query works in SQL Server Management Studio (2008 R2):

    select * from [dbo].[Music] where [id] = 1

    This SQL from the EF also works in SQL Server Managenent Studio (2008 R2):

    {SELECT [Extent1].[id] AS [id], [Extent1].[songName] AS [songName], [Extent1].[album] AS [album], [Extent1].[artist] AS [artist], [Extent1].[time] AS [time], [Extent1].[music] AS [music] FROM [dbo].[Music] AS [Extent1]} where id = 1

    The only place these quries fail to provide the desired result is when running under EF!

    Sunday, December 9, 2012 7:42 PM
  • User1779468316 posted

    Any news from the seniour development team yet?

     

    Thursday, December 13, 2012 5:29 PM
  • User1779468316 posted

    I was able to get it working by reveriting back to ADO.Net code. This just reinforces my idea that EF is BROKEN for my use case.

    Tuesday, December 18, 2012 11:36 AM
  • User-1694785820 posted

    Hi Eric,

    Using the code that you had pasted earlier, I tried to reproduce the issue in sample windows forms applicatin but for me it only returns one record. I used the exact same code that you pasted with only the Music Entity and also with the same lambda expression i.e. db.MusicData.Where( m => m.id == id )

    I would recommend you to open a support case with us to determine the cause. Visit this link to see the various support options that are available to better meet your needs: http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone."

    Thanks,

    Kunal

    MSFT

    Monday, January 21, 2013 9:34 AM
  • User1779468316 posted

    Hi Eric,

    Using the code that you had pasted earlier, I tried to reproduce the issue in sample windows forms applicatin but for me it only returns one record. I used the exact same code that you pasted with only the Music Entity and also with the same lambda expression i.e. db.MusicData.Where( m => m.id == id )

    I would recommend you to open a support case with us to determine the cause. Visit this link to see the various support options that are available to better meet your needs: http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone."

    Thanks,

    Kunal

    MSFT

    I did report this BUG with Microsoft a while back in thier bug reporting site. I don't have the money to get paid support yet and won't until after I find a job again and fix the things that need fixing here.

     

    Monday, January 21, 2013 10:30 AM
  • User2053451246 posted

    That is why the Primary key on the Music table is a multi-part key:

    id
    songName
    album
    artist

    You say that the above is your "multi-part key".  If that's the case, instead of using m => m.id == id to get a record you need use all of the fields that make up the key.

    Monday, January 21, 2013 12:19 PM
  • User1779468316 posted

    Because the id is not unquiet. As the member is allowed to upload mutuable songs and they are all stored with his/her id number.

    That is how we have more than one record per id number.

    Adding an uniquit key to the table doesn't really solve anything here because we still need to pull the records by id number.

    Monday, January 21, 2013 3:11 PM
  • User-1990808279 posted

    I know this is a very old question, but I figured I'd post my answer in hopes that it might help someone in the future. After banging my head on the keyboard for a little I was able to figure it out. I had the same issue as the original poster. I would get the correct number of row back through entity framework, but every record was essentially a duplicate of the first. It boiled down to being a problem with retrieving records with no unique id as posted above. I was able to solve it by adding the AsNoTracking to the query.

    context.CustomRecords.AsNoTracking() // removed clauses for simplicity...

    Doing this I was able to retrieve the correct data.

    Friday, June 12, 2015 4:31 PM
  • User-1804197839 posted

    Fantastic!  Thank you for posting this, ms2474

    I was having similar problem this morning until I stumbled on to this thread. I had a hunch that it is something to do with how the data is stored or retrived, because in my case this was a view in mssql db that returned 94 identical records (same as first record). 

    Your suggestion to use 

    .AsNoTracking()

    worked like a charm. 

    The real reason why this does not work appears to be that there was no ID in the selected results (at least in my case) 

    Wednesday, February 7, 2018 7:39 AM