using linq to select multiple columns with 1 unique one
Posted: Thu 22 Apr 2010 00:30
I am selecting columns from two tables. A header table (with a unique reference) and a names table. The header table may have multiple names associated and so the new selected table can have rows of columns from both tables which has multiple rows containing the unique reference from the header table.
I would lime the resulting table to only have one row for each unique reference. I am not bothered which of the lines from the joined data is used so long as it is one. e.g. the current linq query is:
var query = from Header in myTables.TblHead
from Name in Header.TblNames
where Header.Name.ToUpper().Contains(surname.ToUpper())
|| Name.Company.ToUpper().Contains(surname.ToUpper())
|| Name.Surname.ToUpper().Contains(surname.ToUpper())
orderby Header.Reference, Header.Name
select new
{
Header.Reference,
Header.Name,
Name.Surname,
Name.Company
};
So if the constraints has 4 rows in TblNames linked to TblHead with reference "ABC" I will have 4 rows in the query result. I would like to have one row in the result since the first two columns are the most important and the last two columns are useful but not essential hence it doesn't matter which one is shown.
I have googled so many sites trying all sorts of recommendations, but none of them succeed. Would be greatful is someone can help.
I am using c# in VS 2008 with devart to linq to my Postgresql db.
Regards
I would lime the resulting table to only have one row for each unique reference. I am not bothered which of the lines from the joined data is used so long as it is one. e.g. the current linq query is:
var query = from Header in myTables.TblHead
from Name in Header.TblNames
where Header.Name.ToUpper().Contains(surname.ToUpper())
|| Name.Company.ToUpper().Contains(surname.ToUpper())
|| Name.Surname.ToUpper().Contains(surname.ToUpper())
orderby Header.Reference, Header.Name
select new
{
Header.Reference,
Header.Name,
Name.Surname,
Name.Company
};
So if the constraints has 4 rows in TblNames linked to TblHead with reference "ABC" I will have 4 rows in the query result. I would like to have one row in the result since the first two columns are the most important and the last two columns are useful but not essential hence it doesn't matter which one is shown.
I have googled so many sites trying all sorts of recommendations, but none of them succeed. Would be greatful is someone can help.
I am using c# in VS 2008 with devart to linq to my Postgresql db.
Regards