Return only a single row from duplicate rows with MySQL

Question

I have an ASP.NET Core Web App (MVC) 3.1 project using Entity Framework Core to generate a model for a SQL database using Nuget Console.

When data is pulled into jQuery. Datatable shows a duplicate result because of the extension. I only need the 4 digi ext. rows.

FirstName  |  LastName | Email            | Title     |Number 
---------------------------------------------------------
John       |  Doe     | ******@exmaple.com  | Accountant| 6543

John       |  Doe     | ******@exmaple.com  | Accountant| 76543

How can I eliminate the row with the 5 digi ext. 


 using (newMA4000Context _context = new newMA4000Context())
                {

                    List<UserExtensionVw> userExtensionVws1 = _context.UserExtensionVws.ToList();
                    List<UserVw> userExtensionVws2 = _context.UserVws.ToList();

              
                    var TableJoin = from e in userExtensionVws1
                                    join d in userExtensionVws2 on e.Userid equals d.Userid into table
                                    from d in table.ToList()

                                    

                                    select new MA400_ViewModel
                                    {

                                        GetUserExtensionVw = e,
                                        GetUserVw = d


                                    };


                

                return View(TableJoin.ToList());
                }

Answer 1

Hi @Oneil Drummond

List<UserExtensionVw> userExtensionVws1 = _context.UserExtensionVws.ToList();

From your query statement and result data, I assume the userExtensionVws1 might contains the duplicate UserExtensionVw, which has the same Userid with different Number, right? like this:

Then, if we directly use the query statement, it will get your current result.

To get your required result, before joining the table, you can group the userExtensionVws1 and sort each group by the number property, then get the first item of each group. Code like this:

    List<UserExtensionVw> newuserExtensionVws = userExtensionVws1.GroupBy(c => c.Userid).Select(g => g.OrderBy(p => p.Number).First()).ToList();

After that use the filter result to join the tables.

            List<UserExtensionVw> newuserExtensionVws = userExtensionVws1.GroupBy(c => c.Userid).Select(g => g.OrderBy(p => p.Number).First()).ToList();

            var TableJoin = from e in newuserExtensionVws 
                             join d in userExtensionVws2 on e.Userid equals d.Userid into table
                            from d in table.ToList()  
                            select new MA400_ViewModel
                            { 
                                GetUserExtensionVw = e,
                                GetUserVw = d 
                            };
             
            return View(TableJoin.ToList());

The result as below:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

Answer 2

Create view with only the columns you want and use a GROUP BY or DISTINCT. See the MySQL doc for how to create a View. See the EF docs for calling a View.

Keyless Entity Types

Another option is create a raw query.

SQL Queries