Linq to Sql Query returning same record twice

Question

I am querying the database using Linq to Sql. Here is my data :

     Name      LastName        Age
    ------------------------------
1    Abc         Def            15
2    Abc         Def            17
3    xyz         wss            17

My Linq to Sql Code is :

Context _context = new Context("Conn_String");
var table = _context.GetTable<Person>();
List<Person> persons = table.Where<Person>(p => p.Name == "Abc" && p.LastName == "Def").ToList<Person>();

According to my understanding, This query should return 2 records. i.e. Record 1 and Record 2. But it is returning Record 1 twice. Can you enlighten me if it is a bug in Linq to Sql or something I am doing wrong?

EDIT:

This is my DAL Code:

public List<T> GetList<T>(Expression<Func<T, bool>> predicate) where T : class
{
     try
     {
          Context _context = new Context("Conn_String");
          var table = _context.GetTable<T>();
          return table.Where<T>(predicate).ToList<T>();
      }
      catch (Exception ex)
      {
           throw ex;
      }
 }

I am calling this method as :

List<Person> person = DAL.GetList<Person>(p => p.Name == "Abc" && p.LastName == "Def");

foreach(var Person in persons )
{
    // Print(person.Age);
}

Chances are there's something wrong with your diagnostics. Please show how you're determining that the same record is being returned twice. — Jon Skeet
– Jon Skeet, Commented Nov 18, 2012 at 8:26
Yes. I use LINQ (though not to SQL) extensively for years in data warehouse projects among other things and never saw that. I would start with debugging the SQL level - see the generated statement on the database, then see what sql server returns to this by running it in management application. This is 99.9% a wrong diagnosis. — TomTom
– TomTom, Commented Nov 18, 2012 at 8:27
Show us how you iterate the records to do the diagnoses. There is nothing wrong with your query (ok, a lot of code, you can do it way more simple: List<Persons> persons = db.Persons.Where().Tolist() ) — Pleun
– Pleun, Commented Nov 18, 2012 at 20:58

ric cook · Accepted Answer · 2013-10-31 02:16:52Z

7

I've just run into this issue myself. Check which property the model has inferred to be your entity key for your Person class.

If it has a non-unique column as the entity key it will will use the first row which matches the value for each row when converting using ToList()

answered Oct 31, 2013 at 2:16

ric cook

711 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Khaksar · Accepted Answer · 2019-07-11 07:35:45Z

2

Short answer, You need to add the Primary key in the data your fetching.

You can simply add a column to your view select statement which is unique. You don’t have to use that column in your program but it will allow EF to properly build each object for you.

Although the question was asked in Nov 2012 and I am answering in Jul 2019. But the detailed answer is available at the below source. I am answering maybe thing might help someone out there.

LINQ in .NET returning duplicate rows from a working SQL view https://www.itworld.com/article/2833108/linq-in--net-returning-duplicate-rows-from-a-working-sql-view--solved-.html

answered Jul 11, 2019 at 7:35

Khaksar

3434 silver badges19 bronze badges

Comments

Sunny · Accepted Answer · 2012-11-19 05:29:25Z

1

Hi Usman,

This is my table :

enter image description here

Here i am using the following query :

   using (DataClassesDataContext dc = new DataClassesDataContext())
        {
            var v = (from c in dc.t_controls where (c.config_display == "SHOW_VA" && c.next_value == 1) select c.description).ToList();
        }

This query returns all the 5 rows.Take it as reference and check where are you wrong.Hope it help you.

answered Nov 19, 2012 at 5:29

Sunny

3,3359 gold badges37 silver badges69 bronze badges

2 Comments

Usman Khalid Over a year ago

I am not using syntax (from c in dc.t_controls)....... I am using Context _context = new Context("Conn_String"); var table = _context.GetTable<T>(); return table.Where<T>(predicate).ToList<T>(); May be that's why I am facing this behavior.

Sunny Over a year ago

May be,But have you solved your problem by using my solution or not ?

Bart Czernicki · Accepted Answer · 2012-11-18 18:46:36Z

1

Can you run your query and profile it using SQL Server profiler (assuming SQL Server) in the database. I am wondering if you have duplicate records in your table..or if there is a join causing duplicates.

I have used LINQ to SQL with no problems.

If the database profile comes out correct and you want to "force it" to be unique you can always pass a distinct method in your LINQ query at the end.

answered Nov 18, 2012 at 18:46

Bart Czernicki

3,6751 gold badge23 silver badges19 bronze badges

Collectives™ on Stack Overflow

Linq to Sql Query returning same record twice

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related