Is LINQ implementation of Entity Framework inappropriate?

Question

Tested objects' classes as preamble

namespace EFConsoleApp.Models.Db
{
    [Table("a")]
    public class TableA
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }
        [Column("title")]
        public string Title { get; set; }
        [Column("amount")]
        public int Amount { get; set; }
        [Column("a_id")]
        public int AId { get; set; }
    }
}

namespace EFConsoleApp.DataAccesses.Db
{
    public class ContextA : DbContext
    {
        public string DefaultSchema { get; private set; }

        public ContextA() : base(GetConnecting(), true)
        {
            DefaultSchema = "public";
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
            => modelBuilder.HasDefaultSchema(DefaultSchema);

        public static NpgsqlConnection GetConnecting()
        {
            return new NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgreSql"].ToString());
        }

        public virtual DbSet<TableA> A { get; set; }
    }
}

Question

Why I get the following exception when executing the code below with no data in table?

※ I'm using Entity Framework 6.2.0 and Npgsql 4.0.7 and .NET Framework 4.6 .

Exception:

System.InvalidOperationException:
The cast to value type 'System.Int32' failed because the materialized value is null.
Either the result type's generic parameter or the query must use a nullable type.

Executed code:

// _ctx's type is DataAccesses.Db.ContextA .
// ContextA table has no data.

var sum = _ctx.A
    .Where(e => e.AId == aid)
    .Select(e => e.Amount)
    // .ToString() shows "SELECT \"Extent1\".\"amount\" FROM \"public\".\"a\" AS \"Extent1\" WHERE \"Extent1\".\"a_id\" = @p__linq__0"
    .Sum();

And why I don't get the same exception when executing the code below with no data in list?

var list = new List<TableA>();
var qList = list.AsQueryable(); // To imitate return type of .Select()

// qList has no data.

var sum = qList
    .Where(e => e.AId == 1)
    .Select(e => e.Amount)
    .Sum(); // sum = 0

Called LINQ methods are the same (as I think...), but result is different.

There are also other attempts

Attempt #1

var sum = <Resource>
    .Where(e => e.AId == a
    .Sum(e => e.Amount);

In case of EF, the same exception.
In case of in-memory object (new List<TableA>().AsQueryable()), sum is 0.

Attempt #2

var sum = (from a in <Resource> where a.AId == aid select a.Amount).Sum();

In case of EF, the same exception.
In case of in-memory object (new List<TableA>().AsQueryable()), sum is 0.

Attempt #3 (Tested only with EF)

var sum = _ctx.Database
    .SqlQuery<int>("SELECT amount FROM a WHERE a_id = @par_a_id;",
        new NpgsqlParameter("par_a_id", aid))
    .Sum();

Surprisingly it works and sum is 0.

---

So, what's wrong with EF's implementation of LINQ?
Or problem with me because I can't understand something how it works under hood?

---

Update against @MindSwipe 's answer

I tried to execute sql with SUM directly in database and EF.
※ Still with no data in table.

SELECT SUM(amount) FROM a WHERE a_id = 1;

Query above returns null.

var sum = _ctx.Database
    .SqlQuery<int>("SELECT SUM(amount) FROM a WHERE a_id = @par_a_id;",
        new NpgsqlParameter("par_a_id", aid))
    .Single();

Code above cause the same exception.

System.InvalidOperationException:
The cast to value type 'System.Int32' failed because the materialized value is null.
Either the result type's generic parameter or the query must use a nullable type.

So, at least I understood why exception occurs.

As conclusion

In case to avoid exceptions when working with SUM,
obviously, the code below is the most safe?

var sum = _ctx.Database
    .Where(e => e.AId == aid)
    .Select(e => e.Amount)
    .DefaultIfEmpty(0)
    .Sum();

Update against @Aron 's comment

Using IDbCommandInterceptor I got raw sql.

var sum = _ctx.A
    .Where(e => e.AId == aid)
    .Select(e => e.Amount) // #1
    .Sum(); // #2

-- #1
SELECT                                                "Extent1"."amount"                   FROM "public"."a" AS "Extent1" WHERE "Extent1"."a_id" = @p__linq__0"
-- #2
SELECT "GroupBy1"."A1" AS "C1" FROM (SELECT CAST (sum("Extent1"."amount") AS int4) AS "A1" FROM "public"."a" AS "Extent1" WHERE "Extent1"."a_id" = @p__linq__0) AS "GroupBy1"

Answer 1

Edit: Major re-write of the answer, the old one was incorrect.

---

In PostgresSQL SUM returns null when there are no elements to sum up. To test this, here's a short script:

drop table if exists temp;
create table temp (id integer, amount integer);
insert into temp (id, amount) values (1, 0);
select SUM(amount) from temp where id = 2;

Check the output and it will be null, not 0 or any other number

You can copy and paste the script into this site if you don't want to use a local database.

To protect against this case you need to either

1. Cast select nullable amount, and sum it, returning 0 if none were found, like so:

var sum = _ctx.A
    .Where(x => x.AId == aid)
    .Select(x => (int?) x.Amount)
    .Sum() ?? 0;

2. Make Amount nullable by making it be public int? Amount {get; set;}