CODEWITHSUNDEEP
c#linqsqlitelinq-to-sqlinner-join
rock
rock

Reputation: 705

Linq Query to Get Distinct Records from Two Tables

I have two Tables - tblExpenses and tblCategories as follows

tblExpenses

ID (PK),
Place,
DateSpent,
CategoryID (FK)

tblCategory

ID (PK),
Name

I tried various LINQ approaches to get all distinct records from the above two tables but not with much success. I tried using UNION and DISTINCT but it didnt work.

The above two tables are defined in my Model section of my project which in turn will create tables in SQLite. I need to retrieve all the distinct records from both the tables to display values in gridview.

Kindly provide me some inputs to accomplish this task. I did some research to find answer to this question but nothing seemed close to what I wanted. Excuse me if I duplicated this question.

Here is the UNION, DISTINCT approaches I tried:

DISTINCT # ==> Gives me Repetitive values

(from exp in db.Table<tblExpenses >()
                                   from cat in db.Table<tblCategory>()                                       
                                   select new { exp.Id, exp.CategoryId, exp.DateSpent, exp.Expense, exp.Place, cat.Name }).Distinct();

UNION # ==> Got an error while using UNION

Upvotes: 3

Views: 5206

Answers (3)

Jacco
Jacco

Reputation: 3272

You will always get DISTINCT records, since you are selecting the tblExpenses.ID too. (Unless there are multiple categories with the same ID. But that of course would be really, really bad design.)

Remember, when making a JOIN in LINQ, both field names and data types should be the same. Is the field tblExpenses.CategoryID a nullable field?

If so, try this JOIN:

db.Table<tblExpenses>()
.Join(db.Table<tblCategory>(), 
       exp => new { exp.CategoryId }, 
       cat => new { CategoryId = (int?)cat.ID }, 
       (exp, cat) => new { 
                           exp.Id, 
                           exp.CategoryId, 
                           exp.DateSpent, 
                           exp.Expense, 
                           exp.Place, 
                           cat.Name 
                         })
.Select(j => new { 
                   j.Id, 
                   j.CategoryId, 
                   j.DateSpent, 
                   j.Expense, 
                   j.Place, 
                   j.Name 
                 });

Upvotes: 1

bot
bot

Reputation: 4921

You can try this queries:

A SELECT DISTINCT query like this:

SELECT DISTINCT Name FROM tblCategory INNER JOIN tblExpenses ON tblCategory.categoryID = tblExpenses.categoryID;

limits the results to unique values in the output field. The query results are not updateable.

or

A SELECT DISTINCTROW query like this:

SELECT DISTINCTROW Name FROM tblCategory INNER JOIN tblExpenses ON tblCategory.categoryID = tblExpenses.categoryID;<br/><br/>

looks at the entire underlying tables, not just the output fields, to find unique rows.

reference:http://www.fmsinc.com/microsoftaccess/query/distinct_vs_distinctrow/unique_values_records.asp

Upvotes: 0

COLD TOLD
COLD TOLD

Reputation: 13599

I think union already does the distict when you join the two tables you can try somethin like 

 var query=(from c in db.tblExpenses select c).Concat(from c in 
            db.tblCategory select c).Distinct().ToList();

Upvotes: 3

Related Questions