using linq to select multiple columns with 1 unique one

[kerrywales]

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

[AndreyR]

There is no easy way to create a LINQ query implementing the scenario you are describing.
Try to write plain SQL solving the problem and then execute this SQL using the DataContext.Query method.

[kerrywales]

Thanks for your response but I have worked out a solution by flipping the search on its head.

var query = from Name in entity.TblName
where Name.TblHead.Name.ToUpper().Contains(surname.ToUpper())
|| Name.TblName.Company.ToUpper().Contains(surname.ToUpper())
|| Name.TblName.Surname.ToUpper().Contains(surname.ToUpper())
group Name by Name.TblHead.Reference into g
select new
{
g.FirstOrDefault().TblHead.Reference,
Name = g.FirstOrDefault().TblHead.Name.TrimEnd(),
Surname = g.FirstOrDefault().TblName.Surname.TrimEnd(),
Company = g.FirstOrDefault().TblName.Company.TrimEnd(),
MatchesInHead = g.Count()
}

This gives me the one Head Reference that I want and also tells me how many names (including the one on display) are linked to the Reference I want. TblHead and TblName are linked in the constraints definition within the database.

Kerry

[AndreyR]

Thank you for sharing your knowledge.