How to get unique records from database?

Question

How can I get unique records from database by linq?

For example: I have something like that

I would like to have something like that

07.12.2016
08.12.2016
10.12.2016

My query:

var query2 = (from availableDate in bazaDC.seanses
                        where availableDate.id_filmu == id
                        orderby availableDate.data ascending
                        select availableDate.data).Distinct();
availableDate.DataSource = query2;
availableDate.DataBind();

And html:

<asp:Repeater ID="dostepneDaty" runat="server">
    <ItemTemplate>
        <a href="repertuar_szczegoly.aspx?id=<%# Eval("id_filmu") %>&&data=<%# Eval("data","{0:dd.MM.yyyy}") %>"><%# Eval("data","{0:dd.MM.yyyy}") %></a><br />
    </ItemTemplate>
</asp:Repeater>

Distinct() doesnt work.

And Im going to include table definition:

CREATE TABLE [dbo].[seanse] (
    [id_seansu] INT      IDENTITY (1, 1) NOT NULL,
    [godzina]   TIME (7) NOT NULL,
    [data]      DATE     NOT NULL,
    [id_filmu]  INT      NOT NULL,
    PRIMARY KEY CLUSTERED ([id_seansu] ASC),
    CONSTRAINT [FK_seanse_ToTable] FOREIGN KEY ([id_filmu]) REFERENCES [dbo].[filmy] ([id_filmu])
);

Any ideas?

Is `GroupBy()` appropriate? [MSDN](https://msdn.microsoft.com/en-us/library/system.linq.enumerable.groupby(v=vs.110).aspx), [SO](http://stackoverflow.com/questions/7325278/group-by-in-linq) — vbnet3d, Dec 23 '16 at 17:06

score 4 · Answer 1 · answered Dec 23 '16 at 17:15

From what I know your select distinct should work, but you can always do the same thing by grouping and selecting the first element.

Examples Below:

Select Distinct:

 var query2 = bazaDC.seanses
             .Where(availableDate => availableDate.id_filmu == id)
             .Select(x => x.data)
             .Distinct()

Group By:

 var query2 = bazaDC.seanses
             .Where(availableDate => availableDate.id_filmu == id)
             .GroupBy(x => x.data )
             .Select(g => g.First())

Christian Melendez · Accepted Answer · 2016-12-23T18:37:29.620

2

Try this piece of code

 var query2 = (from availableDate in bazaDC.seanses
                     where availableDate.id_filmu == id
                     group availableDate by new { availableDate.id_filmu, availableDate.data } into g
                     orderby availableDate.data ascending
                     select new { g.Key.id_filmu, g.Key.data});

You're adding the group by clause so it will retrieve unique values

edited Dec 23 '16 at 18:37

answered Dec 23 '16 at 17:10

Christian Melendez

376
1
10

Everything fine but I have to get id_filmu aswell bucause Im using that in repeater ID="dostepneDaty", you know... So from query2 I have to get two fields, id_filmu and data. – Arek Wiśniewski Dec 23 '16 at 17:37
oh ok, then you will need to group by date and id_filmu, check the updated answer in a few seconds – Christian Melendez Dec 23 '16 at 17:43
How can I do it with two field to groupby? What is the syntax? – Arek Wiśniewski Dec 23 '16 at 17:49
Not working, many errors. I think, I cant do it like that: select g.key.id_filmu, g.key.data I mean I cant have 2 fields in select. – Arek Wiśniewski Dec 23 '16 at 18:12
Yep, I got it. Ur code is correct but in select I had to add something like that: select new {g.Key.id_filmu, g.Key.data }) Cheers guys. – Arek Wiśniewski Dec 23 '16 at 18:20
You're right!! I added the missing piece to the answer – Christian Melendez Dec 23 '16 at 18:37

Andy · Answer 3 · 2016-12-23T17:31:20.340

1

I created a dotnetfiddle example showing it working as expected. Below is the LINQ statement used in that example.

IEnumerable<DateTime> dataSource = (from availableDate in seanses
    where availableDate.id_filmu == 1
    orderby availableDate.data
    select availableDate.data).Distinct();

edited Dec 23 '16 at 17:31

answered Dec 23 '16 at 17:19

Andy

648
8
21

How to get unique records from database?

3 Answers3