Clever Geek Handbook

Linq query to get separate 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 have tried various LINQ approaches to get all the individual records from these two tables, but with no great success. I tried to use UNION and DISTINCT but it didn't work.

The above two tables are defined in the My Project section of my project, which in turn will create tables in SQLite. I need to get all the individual records from both tables in order to display the values ​​in the gridview.

Please provide me with some materials to complete this task. I did some research to find the answer to this question, but nothing seemed to come close to what I wanted. Sorry if I repeat this question.

Here is my UNION, DISTINCT approach that I tried:

DISTINCT # ==> Gives me duplicate 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 # ==> There was an error when using UNION

+3


source to share


3 answers


I think the union already makes a distict when you join two tables you can try like



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


source


You will always receive entries DISTINCT

as you also choose tblExpenses.ID

. (Unless there are multiple categories with the same ID, but that would of course be really, really bad design.)

Remember that when created JOIN

in LINQ, both field names and data types must be the same. tblExpenses.CategoryID

Is the field 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 
                 });
+1


source


You can try the following queries:

A SELECT DISTINCT run the following queries:

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

limits the results to unique values ​​in the output field. Query results are not updated.

or

A SELECT DISTINCTROW query:

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

looks at all base tables, not just output fields, to find unique rows.

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

0


source






More articles:


All Articles