CODEWITHSUNDEEP
c#asp.net.netdatabaselinq-to-sql
Usman Khalid
Usman Khalid

Reputation: 3110

Linq to Sql Query returning same record twice

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);
}

Upvotes: 3

Views: 2878

Answers (4)

Khaksar
Khaksar

Reputation: 343

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

Upvotes: 2

ric cook
ric cook

Reputation: 71

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()

Upvotes: 7

Sunny
Sunny

Reputation: 3295

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.

Upvotes: 1

Bart Czernicki
Bart Czernicki

Reputation: 3683

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.

Upvotes: 1

Related Questions